May 02 2018 11:47 AM
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
May 02 2018 04:28 PM
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)
May 03 2018 11:31 AM
I'm moving this question to the Excel community for better visibility.
May 03 2018 11:40 AM - edited May 03 2018 11:41 AM
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 |
May 03 2018 11:53 AM
With small comment, with filtered rows it doesn't matter what to use, 9 or 109