Cumulative SUM in Excel
If you're a frequent user of the
This can be easily accomplished using relative and absolute cell references combined with the
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 (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
As we copy and paste this formula down to our other cells, we want the initial cell in our range to stay put — at
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,
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...