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

ABCD
1RegionSalesRunning total
2North12001200
3South800=SUM($B$2:B3)
4West400=SUM($B$2:B4)
5East1600=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

  1. The start of the range, $B$2, is locked with $ signs, so it never moves.
  2. The end of the range, B3, is not locked, so it moves down one row for every row the formula is filled.
  3. Each row therefore adds up everything from the top down to itself.

Variations

  1. The total down to the second row

    ABCDEF
    1RegionSalesRunning total
    2North12002000
    3South800=SUM($B$2:B3)
    4West400=SUM($B$2:B4)
    5East1600=SUM($B$2:B5)

    In F2: =SUM($B$2:B3) → 2000

    1,200 + 800.

  2. As a share of the whole

    ABCDEF
    1RegionSalesRunning total
    2North120050%
    3South800=SUM($B$2:B3)
    4West400=SUM($B$2:B4)
    5East1600=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

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

Practise it

Related