Calculating Filtered Values - The Subtotal

Excel filters are great, but wouldnt it be powerful to be able the to run calculations on the data as the filters change? The underutilized SUBTOTAL function should be added to your tool belt.

Syntax:
SUBTOTAL(Function,ref1,ref2,..)
There are seperate functions that will include or ignore Hidden values.
Note: Subtotal will automatically ignore any hidden data that is hidden becuase of the use of a filter.

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

Lets look at an example:
The example below is counting the number of entries currently shown.

We have a list of fruit that we want to count.
In cell B2 the forumula =SUBTOTAL(103,C3:C35) will (CountA, the Range C3:C35), there are 33 records.

Note: CountA returns the number of non blank cells (Used to count cells with text or numbers)
          Count returns the number of cells that contain a number. (Used to count cells with numbers only)

If we add a filter to only show Apples and Oranges, this list shrinks.

The Subtotal is now 21, the calculation is ignoring the hidden data.

The next time your working with Filteres or hidden data, dont forget to make use of SUBTOTAL.