SUBTOTAL Formula
The mention of the subtotal formula will probably cause you to relate the formula to calculating the total for sub-groups of data. This is generally true. But the formula can also be used to calculate average, count numbers, count text and number, find maximum, find minimum, addition and 5 other types of calculation. That was what our participants in “Hidden Secrets in Data Analysis with Excel” found out. Neither do they think they should be trained in this formula or realised how powerful it can become
when it is combined with the auto-filter function.
Let me share with you what is this formula and how it works.
Subtotal formula requires 2 basic inputs, the function number and a range.
The function number determines the type of calculation to be performed with the formula. If the function number 1 is used, it calculates the average of the numbers found in the range listed. If the function number is 2, it is used to count numbers. See below for the complete list of calculations you can do with formula.
[supsystic-tables id=30]
The range defines the cells that should be involved in the calculation. This is usually entered as one continuous range. But it doesn’t have to be limited to one continuous range. You can list multiple ranges in the formula.
From the above explanation, you probably think that it is a good substitute for the SUM, COUNT, COUNTA, AVERAGE formulas. That is true if you are using it in isolation. But if you use the Subtotal formula together with Auto-filter, it will calculate the results based on the filtered rows only. Hidden rows are excluded from the calculation even though the range is included in the formula.
For example, in a list of 20 items, the Subtotal formula will return 20 as the answer. The COUNTA formula will also return the result as 20. But if you filter the list based on a certain criteria, some of the rows are hidden. If you look at the Subtotal formula again, you will find that it counts the number of rows that are visible to you. As for the COUNTA formula, it continues to show the number 20.
Learn about the SUBTOTAL function.
