Excel track · Exercise 5 of 86 Paid
Debug a total that skips a row
Find the row a broken total skips and cover it without double-counting a subtotal.
Advanced
City sales with a gap, and four months plus a subtotal
Read-only data · screen 1 of 2 · answer in B8
In B8, total every store's sales even though one cell in the column holds text.
| A | B | |
|---|---|---|
| 1 | Store | Sales |
| 2 | Brooklyn | 320 |
| 3 | Queens | 250 |
| 4 | Bronx | n/a |
| 5 | Harlem | 300 |
| 6 | Astoria | 400 |
| 7 | Fulton | 180 |
| 8 | City total |
In B7, total January to April without counting the Q1 subtotal row above it.
| A | B | |
|---|---|---|
| 1 | Month | Amount |
| 2 | Jan | 320 |
| 3 | Feb | 250 |
| 4 | Mar | 180 |
| 5 | Apr | 140 |
| 6 | Q1 total | 750 |
| 7 | Four months |
Reference answer
What it does
A total that skips a row is nearly always a range problem. The range stops one short, starts one late, or reaches up into a subtotal it should have stayed below. SUM is doing what it was told.
On the first screen one cell in the middle holds text, and SUM steps over it without complaint, so B2:B7 returns 1450. A range drawn by hand around the text stops above the stores further down.
On the second, the row above the answer is a Q1 subtotal of 750, and a range that touches it counts the quarter twice: 890 becomes 1640. Compare the total with a quick manual addition, then select the answer cell and read the highlighted block, which is exactly what the formula sees.
Syntax
=SUM(first_cell:last_cell)Worked example
On City sales with a gap, and four months plus a subtotal, =SUM(B2:B7) returns 1450.
=SUM(B2:B7)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | A cell in the range holds text that a chain of plus signs cannot add. SUM over the range would have skipped it. |
#REF! | The range names a column or row that no longer exists after rows were deleted. |
Wrong total | A range that reaches a subtotal counts the same money twice, and a range that stops early leaves a store out. Neither raises an error. |
At work
A monthly close with a subtotal row above the answer, and an imported list with one unreadable cell.