Cumulative SUM in Excel

If you're a frequent user of the SUM function, you may occasionally want to take the cumulative SUM of a value across a table. For example, if you have a table that outlines product sales by month for an entire year, you may want to insert a cumulative SUM column that shows year-to-date sales at the end of each month.

This can be easily accomplished using relative and absolute cell references combined with the SUM function. Let's take a look at how it works.

Before you start, be sure to read up on the basics of using Excel to get familiar with the program as a whole.

Defining the problem

Take a look at the following spreadsheet, which outlines SnackWorld's sales by month.

Let's say we want to add a "year to date sales" column, which calculates the total year's sales as of the end of each month. For example, the "February" year to date sales calculation will sum the sales from both January and February, and the "April" calculation will sum sales from January, February, March, and April.

Cumulative SUMs with cell reference locking

We can solve the above problem by using SUM on a range, using an absolute reference for our initial cell, and a relative reference for our final cell:

=SUM(C$3:C3)
Output: $5,000,000

The above formula may be a bit confusing at first, but notice that it produces the desired result. How does it work?

Since we want to take the SUM of a range to find year-to-date sales numbers, we start by calculating the value for January: C3:C3. Although this looks like a range, it's really just a reference to cell C3, since the range doesn't include any other cells.

As we copy and paste this formula down to our other cells, we want the initial cell in our range to stay put — at C3 — but we want the final cell in the range to change according to what row we're in: C4, C5, C6, etc. So, we use an absolute row reference to lock the row in our initial cell — C$3 — and a relative row reference in our final cell so that it changes as we move from row to row.

Take a look at what happens to our formula as we copy and paste into other cells:

=SUM(C$3:C5)
Output: $25,000,000

The initial cell of our range, C$3, hasn't changed because its row is locked. But the final cell of our range has changed to C5, because it uses a relative reference and has been copied and pasted downwards. We have successfully combined the SUM function with absolute and relative references to find a cumulative SUM across the rows of a table!

Comments