SUBTOTAL
Total what the filter left showing.
Total what a filter has left showing
The result of the job you asked for, over the visible rows
Syntax
=SUBTOTAL(what_to_do, range1, [range2], ...)
| Argument | Required | What goes here |
|---|---|---|
what_to_do | Required | 9 adds up, 1 averages, 2 or 3 count, 4 takes the largest, 5 the smallest. Add 100 to leave hidden rows out. |
range1 | Required | The block to work on, like F2:F30. |
range2 | Optional | The block to work on, like F2:F30. |
How to use SUBTOTAL
SUBTOTAL takes a number saying what to do, then the range. 9 adds, 1 averages, 2 counts numbers, 3 counts anything, 4 is the largest and 5 the smallest.
What makes it worth learning is that it ignores rows a filter has hidden. Filter to one supplier and a SUBTOTAL underneath reports that supplier, while a SUM underneath goes on reporting the lot.
It also skips any other SUBTOTAL inside its range, so a grand total over a column of subtotals does not double count.
Codes over a hundred mean the same jobs and additionally skip rows hidden by hand. 9 and 109 behave identically under a filter and differ only when somebody has hidden a row themselves.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Supplier | Status | Days overdue | Amount | Supplier | Terms (days) | |
| 2 | INV-8801 | Brightwell Paper | Overdue | 12 | 940 | Ashgrove Catering | 30 | |
| 3 | INV-8802 | Calder Utilities | Queried | 47 | 1180 | Brightwell Paper | 30 | |
| 4 | INV-8803 | Meridian Freight | Overdue | 63 | 2450 | Calder Utilities | 14 | |
| 5 | INV-8804 | Ashgrove Catering | On terms | 0 | 405 | Meridian Freight | 45 | |
| 6 | INV-8805 | Brightwell Paper | Overdue | 38 | 760 | Thornbury Print | 60 | |
| 7 | INV-8806 | Thornbury Print | Overdue | 91 | 1320 | |||
| 8 | INV-8807 | Meridian Freight | On terms | 0 | 580 | |||
| 9 | INV-8808 | Calder Utilities | Overdue | 24 | 215 |
| Formula | Result |
|---|---|
=SUBTOTAL(9,E2:E9)The total with nothing filtered, which matches SUM exactly. The difference only shows once a filter is on. | 7850 |
=SUBTOTAL(3,A2:A9)Job 3 counts anything that is not empty, so it counts rows even though the column is text. | 8 |
=SUBTOTAL(1,E2:E9)Job 1 averages. | 981.25 |
=SUBTOTAL(4,E2:E9)-SUBTOTAL(5,E2:E9)Largest less smallest, both taken over whatever the filter is showing. | 2235 |
Notes
- SUBTOTAL respects a filter. SUM does not. That is the entire reason to reach for it.
- It skips other SUBTOTALs in its range, so nested totals do not double count.
Related
A reference tells you what it does. A unit makes you use it.