Excel track · Exercise 76 of 86 Paid
SUBTOTAL: one function number, six answers
Read the function number first, so one range can be added or searched for its largest value.
Advanced
Stock list with a total row that needs a subtotal
Read-only data · screen 1 of 2 · answer in D5
In D5, add up the prices with SUBTOTAL rather than SUM.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Qty | Value |
| 2 | pen | 10 | 2 | |
| 3 | pad | 5 | 4 | |
| 4 | ink | 20 | 3 | |
| 5 | Total stock |
In D5, find the largest price in the table with SUBTOTAL.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Qty | Value |
| 2 | pen | 10 | 2 | |
| 3 | pad | 5 | 4 | |
| 4 | ink | 20 | 3 | |
| 5 | Total stock |
Reference answer
What it does
SUBTOTAL opens with a number instead of a range. That number chooses which of the built-in functions runs: 9 adds, 1 averages, 2 counts, 4 takes the largest. The range follows it. On this sheet =SUBTOTAL(9,B2:B4) adds the three prices to 35, and the second screen swaps in 4 for a maximum of 20.
The number exists for filtered lists. A filter hides rows, a plain SUM keeps adding them, and a SUBTOTAL recalculates against the rows that remain. Numbers from 101 to 111 go further and skip rows hidden by hand, so 9 and 109 agree here only because nothing is hidden.
Both screens point at the same three cells, so the first argument is the only difference between them, and it moves the answer from 35 to 20. Read that number before the range: it decides whether the range is being added or searched for its largest value.
Syntax
=SUBTOTAL(function-number, range) with 9 (or 109) for SUM, 4 for MAX, 5 for MIN, 2 for COUNTWorked example
On Stock list with a total row that needs a subtotal, =SUBTOTAL(9,B2:B4) returns 35.
=SUBTOTAL(9,B2:B4)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | The first argument is missing or is not one of the numbers SUBTOTAL recognises, so nothing says what to calculate. |
#REF! | The range was deleted, so the reference no longer points at any cells. |
Wrong total | The number says COUNT or MAX while the question asked for a sum, and the answer measures something else. |
At work
The total row under a table that gets filtered during a review meeting.