Auto filter is a wonderful thing isn’t it?
With a few clicks of the mouse little dropdowns appear letting you filter your data in a myriad different ways.
Here’s my unfiltered data and the SUM function in action:
And here’s my filtered data:
So what’s the answer? The SUBTOTAL function!
The subtotal function can, if we ask it nicely, ignore any values hidden by the autofilter. It looks like this:
=SUBTOTAL(function_num,ref1,[ref2], . . . . ])
The function_num argument tells the subtotal function what sort of calculation you want it to do:
1 | AVERAGE |
2 | COUNT |
3 | COUNTA |
4 | MAX |
5 | MIN |
6 | PRODUCT |
7 | STDEV |
8 | STDEVP |
9 | SUM |
10 | VAR |
11 | VARP |
So here’s the same example as above using the subtotal function:
Also read: How to Count only Positive values in a given range
Courtesy: happy.co.uk