How to use SUBTOTAL Function in Excel

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:

=SUBTOTAL(function_num, ref)

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.

 

 1  AVERAGE

2  COUNT

3  COUNTA

4  MAX

5  MIN

6  PRODUCT

7  STDEV

8  STDEVP

9  SUM

10  VAR

11  VARP

 

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:

 =SUBTOTAL(3,A1:A2000)

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:

=SUBTOTAL(3,A1:A6)

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.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *