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

Match Single Criteria in Excel

No repeat statistic based on a single condition is used wildly in daily work, for example, count type of products, the duplicate types are not encountered. This article describes the way of no repeats statistics for data in a single ...

Excel Array Formula

An array formula is a special formula that is converted from a normal formula by the Ctrl+Shift+Enter shortcut. Excel automatically adds curly brackets "{}" at the beginning and end of an array formula. The essence of the array formula is ...

Excel/Google Sheets: Full Row Reference

Excel or Google sheets supports both full row references and full column references. This article will talk about the usage of full row references. Full Row reference is another cell reference in Excel or Google Sheets, which is used to ...

Excel/Google Sheets: Full Column Reference

Full column reference is another cell reference in Excel or Google Sheets, which is used to reference the entire column, for example, if you want to reference the entire column A, you can use the following reference format A: A. ...

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

Sidebar