SUBTOTAL in Excel: how to use it
SUBTOTAL only calculates with the rows that are visible after a filter. Function number 9 adds up, 1 gives the average and 3 counts filled cells.
Try the first exercises for freeIn the lesson The SUBTOTAL function
What does SUBTOTAL do?
SUBTOTAL only calculates with the rows that are visible. With the first argument, you choose what it does: 9 adds up, 1 calculates the average, 2 counts the numbers, 3 counts the cells that aren't empty, 4 gives the maximum and 5 the minimum.
You only see the difference from SUM when there's a filter: SUM counts all the rows, including the hidden ones, while SUBTOTAL only counts what you see on your screen. With 109 instead of 9, it also ignores rows you've hidden by hand.
When do you use SUBTOTAL?
- A total under a list that follows the filter: =SUBTOTAL(9,C2:C100).
- The average of the filtered rows: =SUBTOTAL(1,C2:C100).
- How many rows are left after the filter: =SUBTOTAL(3,A2:A100).
- A total that also ignores rows hidden by hand: =SUBTOTAL(109,C2:C100).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =SUBTOTAL(9,C2:C6)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
SUBTOTAL | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | SUBTOTAL |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
function_num | 9 is sum, 1 average, 2 count of numbers, 3 count of filled cells, 4 maximum, 5 minimum. | 9 |
ref | The cells you calculate with. | C2:C6 |
, | A comma separates the arguments from each other. | , |
SUBTOTAL step by step
E1 contains =SUBTOTAL(9,C2:C6). Without a filter this is the same as SUM. With a filter it only adds up the visible rows. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click E1 and type SUBTOTAL with a bracket.
=SUBTOTAL( - Type the function number: 9 to add up. Type a comma.
=SUBTOTAL(9, - Select the cells you're calculating with, C2 to C6.
=SUBTOTAL(9,C2:C6 - Close the bracket and press Enter. Without a filter, that's 3005, just like SUM. If you set a filter, SUBTOTAL only adds up what stays visible.
=SUBTOTAL(9,C2:C6)
More examples
The average
E1 contains =SUBTOTAL(1,C2:C6). With 1, SUBTOTAL calculates the average of the visible rows.
Counting cells that aren't empty
E1 contains =SUBTOTAL(3,A2:A6). With 3, SUBTOTAL counts the cells that aren't empty, even if they contain text.
The maximum
E1 contains =SUBTOTAL(4,C2:C6). With 4 you get the highest visible value, with 5 the lowest.
Where it often goes wrong
- Function number 9 does count rows you hide by hand. Use 109 for that.
Practise this in the lesson The SUBTOTAL function. The first exercises are free.
Frequently asked questions
What's the difference between 9 and 109?
Both ignore filtered rows. 109 also ignores rows you've hidden by hand.
Read next
Sorting and filteringMaking a chartMaking a PivotTableWorking with tablesConditional formatting