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.

This tutorial requires both familiarity with the SUM function and absolute and relative cell references. Be sure to read those tutorials first before continuing.

Defining the problem

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

Cumulative SUM to calculate year to date sales

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:

Cumulative SUM formula with absolute and relative references
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:

Cumulative SUM copied and pasted downwards
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!

Save an hour of work a day with these 5 advanced Excel tricks

Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on getting Excel to do your work for you.

  • How to create beautiful table formatting instantly...
  • Why to rethink the way you do VLOOKUPs...
  • Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...

By submitting this information, you agree to Deskbright's privacy policy and terms of service.