Using the SUBTOTAL() function with the Autofilter feature
If you're using the Autofilter feature, you're probably wondering how to perform calculations on only those values that are being displayed by the filter. The SUBTOTAL() function is the answer.
The syntax of the SUBTOTAL() function is as follows:
=SUBTOTAL(function_num, ref1, ref2...)
where function_num determines what the calculation should be (see below) and ref1, ref2 etc is the range of cells to be calculated.
Possible values for function_num are as follows:
| 1 | AVERAGE |
| 2 | COUNT |
| 3 | COUNTA |
| 4 | MAX |
| 5 | MIN |
| 6 | PRODUCT |
| 7 | STDEV |
| 8 | STDEVP |
| 9 | SUM |
| 10 | VAR |
| 11 | VARP |
For example, to add up the values in column H (let's say this is the Sales Amount column) that are currently visible, enter the following formula:
=SUBTOTAL(9,H2:H20)
This formula will calculate the sum of all the visible values in column H, and assumes that the table has 19 rows, from H2 to H20. If rows 3, 5, 7, 9 and 11 are not visible because they have been filtered out, the subtotal will not include those values. If you then change the filter criteria, then the SUBTOTAL() function will automatically recalculate based on the rows that are visible (i.e. that have not been filtered out).
Based on the table above, it should be easy to see that changing the formula to the following will calculate the AVERAGE rather than the SUM of the visible rows in column H:
=SUBTOTAL(1,H2:H20)
A couple of quick tips on using SUBTOTAL() on filtered lists:
- Data tables tend to to get longer as more rows are added. Consider putting your subtotals at the top of the range not the bottom so you don't have to keep moving the subtotals down to make room for new rows.
- Don't forget to update the formula as you add new rows. Alternatively, consider writing the formula to include the empty rows below the table so you can add new rows and have them automatically included in the calculation.
- You can use the Autosum feature to automatically enter a SUBTOTAL() function to calculate the sum of a selected range of cells, but makes sure that the formula enters the correct cell range. The Autosum function will base it's calculation on the visible cells. If the first visible row is not the first row in the table (i.e. the first row is filtered out) it will not be included when the SUBTOTAL function is created using the Autosum feature.
Join our mailing list
Our Comment Policy.
We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours. One other point to note - if you post a link in your comment, it will automatically be deleted.
Comments on this lesson
hello sir
hello sir
is it possible to display any text same as subtotal formula. i mean if i use subtotal formula with sum of numeric values, it displays the sum for particular sorted list but i want to do it same with the corresponding cell containg name of that item. okay now i explain completely what i need. cell b3 to b11 containing name of 3 different products and cell c3 to c11 is the value of those items in numeric form. i just applied the subtotal formula in cell c20 i.e. =subtotal(9,c2:c11) and its working good but i want cell b20 to behave same like subtotal. like when i sort for product1 cell b20 should display product1. and so on with product2 and product3..... please help me with this.
hello sir
hello sir
is it possible to display any text same as subtotal formula. i mean if i use subtotal formula with sum of numeric values, it displays the sum for particular sorted list but i want to do it same with the corresponding cell containg name of that item. okay now i explain completely what i need. cell b3 to b11 containing name of 3 different products and cell c3 to c11 is the value of those items in numeric form. i just applied the subtotal formula in cell c20 i.e. =subtotal(9,c2:c11) and its working good but i want cell b20 to behave same like subtotal. like when i sort for product1 cell b20 should display product1. and so on with product2 and product3..... please help me with this.
I can't thank you enough for this simple lesson. I spent two weeks weeding through all the forums and technical responses from wizards, all the while getting backlogged on work needing to be done. I had a starting point which is =subtotal(3,) count all, but i learned very quickly that it was a wee bit more than just that. Hence the search for the correct formula.
THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!