How to / Sums and averages
How to calculate a running total in Excel
In the first row, use =SUM($B$2:B2) and fill it down. The range starts at the top and grows by one row each time.
Sales so far, row by row
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sales | Running total | |
| 2 | North | 1200 | 1200 | |
| 3 | South | 800 | =SUM($B$2:B3) | |
| 4 | West | 400 | =SUM($B$2:B4) | |
| 5 | East | 1600 | =SUM($B$2:B5) |
In D2: =SUM($B$2:B2) → 1200
The rows below show the same formula after filling down: the range ends one row lower each time.
How it works
- The start of the range,
$B$2, is locked with $ signs, so it never moves. - The end of the range,
B3, is not locked, so it moves down one row for every row the formula is filled. - Each row therefore adds up everything from the top down to itself.
Variations
The total down to the second row
A B C D E F 1 Region Sales Running total 2 North 1200 2000 3 South 800 =SUM($B$2:B3) 4 West 400 =SUM($B$2:B4) 5 East 1600 =SUM($B$2:B5) In F2:
=SUM($B$2:B3)→ 20001,200 + 800.
As a share of the whole
A B C D E F 1 Region Sales Running total 2 North 1200 50% 3 South 800 =SUM($B$2:B3) 4 West 400 =SUM($B$2:B4) 5 East 1600 =SUM($B$2:B5) In F2:
=SUM($B$2:B3)/SUM($B$2:$B$5)→ 50%Half of all sales come from the first two regions.
Watch out for
- Adding the cell above.
=D2+B3also gives a running total, but it breaks when a row is deleted or the list is sorted. The SUM version does not depend on the row above. - Where the $ signs go. Lock the first corner only.
$B$2:$B$3would give the same total on every row.
Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.
Functions used
- SUM: Adds up numbers, cells and ranges.
- SUMIF: Adds the cells that meet one condition.
- SEQUENCE: Generates a list of sequential numbers that spills into neighbouring cells.