Running total in Excel: a cumulative sum that fills down
A running total adds each row to the rows above it. The one formula with a half-locked range, why it breaks when you sort, and a running count.
A running total in Excel is =SUM($C$2:C2), filled down. The start
of the range is locked and the end is not. So each row adds up every row from the top to
itself.
Here is a freelancer's list of paid invoices for the year. Column D shows how much has come in so far. The last number in D is the income for the year.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Invoice | Client | Amount | Paid so far |
| 2 | 101 | Harbour Cafe | 400 | 400 |
| 3 | 102 | Mill Studio | 250 | 650 |
| 4 | 103 | Harbour Cafe | 600 | 1250 |
| 5 | 104 | Northgate | 1200 | 2450 |
| 6 | 105 | Mill Studio | 350 | 2800 |
| 7 | 106 | Harbour Cafe | 450 | 3250 |
| 8 | 107 | Northgate | 800 | 4050 |
The formula
In D2, type =SUM($C$2:C2). It gives 400. Fill it down to D8.
D3 now holds =SUM($C$2:C3) and gives 650. D8 holds
=SUM($C$2:C8) and gives 4050. The range grows by one row each
time.
The dollar signs on $C$2 stop the start from moving. C2 without dollar signs is free, so it moves down one row with each copy. If the dollar signs are new to you, read absolute vs relative references first.
Two wrong ways, and what they show
No dollar signs. =SUM(C2:C2) filled down gives
400, 250, 600 and so on. Each row adds only itself. The column copies column C, and there is no
error.
Dollar signs on both ends. =SUM($C$2:$C$2)
gives 400 in every row. Nothing moves, so nothing grows.
There is one check that finds both. The last running total must equal the plain total. Type
=SUM(C2:C8) under the list. It gives 4050, the same as D8. Keep
that check on the sheet.
The other way: add the row above
Some people type =C2 in D2. Then in D3 they type
=D2+C3 and fill it down. The numbers are the same: 400, 650, up
to 4050.
It has two problems. First, D2 needs a different formula from the rest. If you fill
=D1+C2 into D2, you get #VALUE!.
D1 is the header, and Excel cannot add text to a number.
Second, it breaks when you delete a row. Delete row 4 and the formula below it points at a cell
that is gone. That row shows #REF!, and so does every row under
it. The SUM version just makes its range one row shorter.
Use the SUM version. It is one formula for the whole column.
What happens when you sort
Sort the list by Amount, largest first. You get no error. But the numbers in D change.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Invoice | Client | Amount | Paid so far |
| 2 | 104 | Northgate | 1200 | 1200 |
| 3 | 107 | Northgate | 800 | 2000 |
| 4 | 103 | Harbour Cafe | 600 | 2600 |
| 5 | 106 | Harbour Cafe | 450 | 3050 |
| 6 | 101 | Harbour Cafe | 400 | 3450 |
| 7 | 105 | Mill Studio | 350 | 3800 |
| 8 | 102 | Mill Studio | 250 | 4050 |
The column still adds from the top down. But the top is now a different invoice. Invoice 101 showed 400 before. Now it shows 3450, the total of the 5 largest invoices.
D8 is still 4050, so the check under the list still passes. Only the rows in between are wrong. A running total only means something in one order, usually date order.
What to do: keep a column that holds the order, like a date or an invoice number. Sort by it again before you read the running total. If you need a sorted view, copy the list and paste it as values first.
What happens when you insert a row
In the middle. Insert a row between invoices 104 and 105. The formulas below it grow to include the new row. But the new row has nothing in D. Copy the formula from the row above into it.
At the bottom. Type a new invoice in row 9, and Excel may or may not fill D9 for you. Check D9. If it is empty, fill D down one more row.
Above the first invoice. This is the one that goes wrong. Insert a row at row 2 and every $C$2 changes to $C$3. Excel moves locked references too, when the cell they point at moves.
Say the new row 2 is an invoice of 300. The last running total still shows 4050. It should be 4350. The new invoice is outside every range.
Your check may not catch this either. A total of C2:C8 also moves, to C3:C9. Fix the first formula to start at $C$2 again, fill it down, and fix the check.
One way to avoid this is to start the range on the header row:
=SUM($C$1:C2). SUM skips text, so the header adds nothing. It
gives 400, as before. A row inserted under the header is now inside the range.
A running count
The same half-locked range works with COUNTIF. It counts how many times a value has appeared so far. Here it answers "which invoice is this for this client?"
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Invoice | Client | Amount | Count | Client total |
| 2 | 101 | Harbour Cafe | 400 | 1 | 400 |
| 3 | 102 | Mill Studio | 250 | 1 | 250 |
| 4 | 103 | Harbour Cafe | 600 | 2 | 1000 |
| 5 | 104 | Northgate | 1200 | 1 | 1200 |
| 6 | 105 | Mill Studio | 350 | 2 | 600 |
| 7 | 106 | Harbour Cafe | 450 | 3 | 1450 |
| 8 | 107 | Northgate | 800 | 2 | 2000 |
In D2, type =COUNTIF($B$2:B2,B2) and fill it down. Row 7
gives 3, because invoice 106 is the third for Harbour Cafe.
In E2, type =SUMIF($B$2:B2,B2,$C$2:C2) and fill it down. That
is a running total for each client. Row 7 gives 1450, which is 400 plus 600 plus 450.
A running count also finds repeats. Any row where the count is 2 or more is a name you have seen before. Filter that column for 1 and you get each client once.
To number the rows 1, 2, 3, use =COUNTA($A$2:A2). It counts the
filled cells from the top to this row. So an empty row does not add 1.
You can practise this in the lessons below. Each lesson is a real sheet you type into, and each answer is checked when you press Enter. The Excel practice page lists every lesson.