Skip to content Skip to main navigation Skip to footer

How to sum filtered cells in Excel

The SUM function returns the summation of cells or a range of cells but when you are working with a table with a filter SUM function will not provide you the expected result. To sum up filtered cells in Excel required the SUBTOTAL function.

SUBTOTAL function

SYNTAX: =SUBTOTAL(function_num,ref1,[ref2],…)

The arguments of the SUBTOTAL function are

  • function_num- (required): function numbers are generally 1 to 11 or 101-111. These numbers specify the functions to use for the SUBTOTAL.
  • ref1- (required): The first named range or reference.
  • ref2- (optional): Named ranges or references. (2-254)

Learn More: How to lock column width in Excel.

Function numbers from 1 to 11 include manually hidden rows

  • 1- AVERAGE
  • 2-COUNT
  • 3-COUNTA
  • 4-MAX
  • 5-MIN
  • 7-STDEV
  • 8-STDEVP
  • 9-SUM
  • 10-VAR
  • 11-VARP

Function numbers from 1 to 11 exclude manually hidden rows

  • 101-AVERAGE
  • 102-COUNT
  • 103-COUNTA
  • 104-MAX
  • 105-MIN
  • 106-PRODUCT
  • 107-STDEV
  • 108-STDEVP
  • 109-SUM
  • 110-VAR
  • 111-VARP

Please be informed that Filtered cells are always excluded.

How to sum filtered cells in Excel

Let’s have an example sheet. Here is the sales amount of different sales person in the month of January, February, and March. We want to filter and know the sales amount of John, Davis, and Nick.


We filter the above three sales person (John, Davis, and Nick) from the table and sum the filtered cells.

sum filtered cells

Read More: How to count characters in Excel.

Explanation of SUBTOTAL function


Here, the function number is 9 and the range of cells is D5:D15

Difference between SUM and SUBTOTAL function

Let’s have an example to differentiate between SUM and SUBTOTAL. See the screenshot below to find the differences.


I hope, This article on how to sum filtered cells in excel is helpful to you. If you have any questions please post a comment below. I will try my best to reply to your question.


There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *