Forum Discussion
Excel Filter and Sum Function
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...
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