Excel Filter and Sum Function

Copper Contributor

I have a question regarding MS Exel filters and sum function.

 

After a filter is applied to a sheet there is a list of rows that are displayed that meet the criteria of the filter. These rows have a column that is either blank or has a number value. I would like to add up the total value of all the filtered rows with number values in them. The way Excel works is it provides the total value of the rows PRIOR to applying the filter. Is there any way to accomplish this ?

 

Thank you

4 Replies

Hi,

 

Better to publish that question for Excel community here on TechCom.

 

To sum you may use

=SUBTOTAL(9,A:A)

or 

=AGGREGATE(9,7,A:A)

 

I'm moving this question to the Excel community for better visibility.

Sergei is dead on here.  Here's a great article on how SubTotal() works with hidden cells in many different scenarios.

 

https://exceljet.net/excel-functions/excel-subtotal-function

 

Here are the values to use in the first parameter Function_Num based on your scenario:

Function Include hidden Ignore hidden
AVERAGE 1 101
COUNT 2 102
COUNTA 3 103
MAX 4 104
MIN 5 105
PRODUCT 6 106
STDEV 7 107
STDEVP 8 108
SUM 9 109
VAR 10 110
VARP 11 111

With small comment, with filtered rows it doesn't matter what to use, 9 or 109