How to sum filtered cells in Excel
November 10, 2022
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
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
- 6-PRODUCT
- 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
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.
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.
0 Comments