Count filtered cells using SUBTOTAL

Here's what might be one of the most versatile formulae in (almost) common usage.

I made a similar posting in March but only from a COUNTA point of view. This attempts to expand that to most of the available options.

SUBTOTAL uses a selection of "subservient" commands to achieve the goal.

Those commands are: 1 - AVERAGE, 2 - COUNT, 3 - COUNTA, 4 - MAX, 5 - MIN,
6 - PRODUCT, 7 - STDEV, 8 - STDEVP, 9 - SUM, 10 - VAR, 11 – VARP

All are formulae in their own right but used within SUBTOTAL allows for filtered lists to be worked with.

Basically, the formula only works with what you can see when a filter has been applied.

I'm really promoting the SUBTOTAL formula and knowledge of the secondary formulae is not paramount in this description although users with better knowledge of the more obscure (to me) statistical formula such as STDEV, STDEVP, VAR and VARP will no doubt recognise the benefits themselves.

I have prepared a small table containing data on customers on the attached workbook. It could be hundreds of rows. Let’s say for example that we need to know how many unmarried females in the 21-30 age group there are. Very useful IMO! Make the appropriate filters to columns B & C and the formula will only count the number of entries returned.

In this example I have chosen 3 (COUNTA) which simply counts entries in non blank cells in the range.

=SUBTOTAL(3,D2:D10)

The syntax is:

=SUBTOTAL(TYPE OF TOTAL, RANGE OF CELLS)

The same principle applies to the others, find the average, find the min/max, sum etc for the filtered data you request. It's very versatile.

Some of the formulae in the worksheet have had IF statements added to avoid errors appearing – (just for demo)

A note of caution – when constructing your worksheet ensure that your cells containing the SUBTOTAL formula and therefore your results are not on the same rows as the range of data. I prefer to put it above the top of the range.

So, there you have it. SUBTOTAL an unusual and clever formula.