Excel’s SUBTOTAL function can dynamically count records in a list that has AutoFilter activated.
It can also show you other information about records within the list. Whenever you change the AutoFilter criteria the SUBTOTAL function updates accordingly. I recently fell short on my knowledge when asked about the SUBTOTAL function. I hope writing this post was as much assistance to me, as I hope it is to you!
The SUBTOTAL function has two arguments as follows:
The function_num refers to one of 11 possible functions that can be used to calculate your subtotal. The 11 functions are listed below, you refer to these functions in your SUBTOTAL by it’s corresponding numerical value as displayed.
The ref is the cell range that you want to subtotal. So for example, if you wanted to subtotal specific records in cells A1 to A2000, you would write your function like so in cell B2 for example:
Then you simply filter the list (the same range) with your required item.
Lets see how to count the number of records in a filtered list based upon the information given below. Enter the following information into a blank worksheet.
A1: Car B1: Model C1:Price
A2: Ford B2: Mondeo C2: £19,000
A3: Vauxhall B3: Corsa C3: £12,000
A4: Seat B4: Altea XL C4: £16,000
A5: Ford B5: Focus C5: £14,000
A6: Ford B6: Mondeo C6: £15,000
Once you have entered these details, you then need to turn on Excel’s AutoFilter feature. To do this, click Data > Filter > AutoFilter. Once switched on Excel will place a drop down arrow next to each column heading.
At the bottom of the list, in cell A8, enter the following forumula:
Now click the arrow for the Car field, and then click Ford. The AutoFilter should now display all the records that contain Ford in the Car field. Because three records are displayed, the subtotal value in cell A8 is 3. Try refiltering the list and select Seat. The subtotal now displays 1 because there is only one Seat record in the list. You may be thinking this is not required for such a small list, but imagine having a list with over 10,000 records, or even over 50,000 records!
I hope this post was useful.