Calculate Cumulative Totals with Excel SUM Function

Today, through a simple example, we will show you how to use one of the most common-used Mathematical functions in excel, the SUM function, to add up the sum.

In our daily life, we keep an account of what we spend every day. For statistical purpose, we would like to know the total expenses in a period containing several consecutive days. The total expense is the expense of the day plus the expense of the previous days. As time passes, this value is accumulated. Since the total expense is dynamic and accumulated day by day, so to calculate the total spending of a period, we can use Excel SUM function with a mix reference.

Look at the example below. To calculate the total expense for a week, the starting day is Monday, and the ending day is Sunday. The user can check the total expense for any “consecutive days from Monday” of the week in the “Total” column.

Calculate Cumulative Totals with Excel SUM Function1

In this example, the value in the “Total” column is the total expenses from Monday to that day.

FORMULA

To sum a range with a locked starting cell, we can use Excel SUM function with a mixed reference. In this example, Monday through Sunday expenses are listed in the “Spend” column, the reference range is B2: B8. The starting day of a week is Monday, so the starting cell for calculating total expense is B2.

In this example, the formula is:

=SUM($B$2:B2)

EXPLANATION

In cell C2, the formula is =SUM($B$2:B2).

Range $B$2:B2 only contains cell B2, so the value in B2 is returned in C2 directly.

Calculate Cumulative Totals with Excel SUM Function1

Copy the formula down to C3 and the formula is updated to =SUM($B$2:B3).

You can notice that after copying the formula by dragging the handle down, the starting cell is still B2. We lock this cell by adding $ in front of the row and column indexes. Therefore, if the formula is copied down, this starting cell will not be changed.

The mixed reference is now correctly extended to $B$2:B3. So, the values in B2 and B3 are added up.

Calculate Cumulative Totals with Excel SUM Function1

As the formula was copied down, B2 was expanded to B3, B4 and up to B8 finally. the spending range was expanded to B8 at the end of the week.

  • To calculate the total expense of Monday, Tuesday and Wednesday, the formula is =SUM($B$2:B4).

Calculate Cumulative Totals with Excel SUM Function1

  • To calculate the total expense of a whole week, the formula is =SUM($B$2:B8).

Calculate Cumulative Totals with Excel SUM Function1

 

Related Functions

  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
Related Posts

Check Dates in chronological order
sort dates in chronological order1

Assume you have a date list that has different date formats, as seen in the accompanying picture. In this instance, Excel's Sort function will fail to sort them appropriately. However, you may convert all various date formats to a particular ...

How to Use 3D SUM Multiple Worksheets
3D sum multiple worksheets1

To sum a range of numbers is straightforward for most Excel users, but do you know how to establish a 3D reference to total the same range of numerous sheets. In this post, I will present the steps for this ...

If Cell Begins with One of Three Supplied Characters
If Cell Begins with One of Three Supplied Characters

If you want to test values to see if they begin with some given specific characters like “x”, ”y”, or “z”, you can create a formula with COUNTIF and SUM functions to return results. EXAMPLE You can see “TRUE” or ...

Average per Week by Formula in Excel
Average per Week 1

We usually apply AVERAGE function or relevant functions to return average directly in Excel worksheet. But in some situations, only applying average relevant functions cannot figure out our problem. Sometimes we can create a formula with functions and mathematical operation ...

How to Sum if Contains an Asterisk
How to Sum if Contains an Asterisk 7

To add numbers together we need to apply SUM function. And if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. If ...

How to Sum in Vertical Range
How to Sum in Vertical Range 8

If we want to add numbers based on some conditions in Excel worksheet, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. In this article, we will introduce you the method ...

How to Sum in Horizontal Range
How to Sum in Horizontal Range9

To add numbers together we need to apply SUM function. But if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. In ...

How to Sum with Criteria and Or Logic in Excel
How to Sum with Criteria and Or Logic in Excel 8

To add numbers together we need to apply SUM function. And if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. If ...

How To Sum the Largest N Values in Excel

Sometimes we may want to sum the largest N numbers or top N numbers in a range. In this article, we will show you the method of “SUM the Largest N Numbers” by a simple formula which consist of SUMPRODUCT ...

How to Sum for Cell Contains Formula Only in Excel
How to Sum for Cell Contains Formula Only in Excel 9

Sometimes values are created by formulas in cells. If we want to sum values which are created by formulas from a range, but some values which are hardcoded also list in the same range, how can we filter out matched ...

Sidebar