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

If Cell Contain Specific Text

This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of ...

Free Family Budget Template (Step-by-step Guide)

This article will show you some free printable family budget templates for google sheet and Microsoft Excel spreadsheet applications and will also explain some of the features or important functions of these templates. This will make it easy for you ...

6 Best Free Wedding Budget Templates

This post will show you some free wedding budget spreadsheet templates for both google sheets and Microsoft Excel Spreadsheet, as well as some explanations of the features or important function points of these templates. You can then easily make modifications ...

Check Dates in chronological order

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

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 ...

Sidebar