Are you intrigued by the idea of seeing the cumulative sum of a sequence of numbers in Google Sheets? Look no further! In this article, we will reveal a simple and effective way to add a column of cumulative totals to your dataset using the SUM function.
What is a Cumulative Total?
A cumulative total, also known as a cumulative sum, is a sequence of partial sums derived from a given sequence of numbers. It updates every time a new number is added to the sequence, by adding the value of the new number to the previous cumulative total. This allows you to track the growth of the sequence and easily determine the final sum at a glance by looking at the last total.
Cumulative totals have a wide range of applications in various fields, including academia and business, where they are extensively used to track expenses, revenues, inventory, and employee hours.
How to Get a Cumulative Total in Google Sheets
To demonstrate how to get a cumulative total in Google Sheets, let’s use the following dataset:
We will show you how to apply a cumulative total to the sequence of numbers in column B, also known as “Daily Expenses”, from the dataset mentioned above.
Using the SUM Function to Get a Cumulative Total in Google Sheets
In this method, we will utilize the SUM function to add the values from the first row of column B to the current row’s value in column B. For example, to find the cumulative total at row 9, the formula would be:
=SUM($B$2:B9)This formula will sum up all the values from row 2 to row 9.
Note that we used dollar sign ($) symbols in the starting cell reference and omitted them in the ending cell reference. This is because we want the starting cell reference to be absolute, while the ending cell reference should be relative. This mixed reference will allow you to create a growing total that is anchored to the initial value.
Here are the steps to get a cumulative total for daily expenses in column C:
- Type the formula
=SUM($B$2:B2)into cell C2. - Drag the fill handle (located in the bottom right corner of the cell) down to copy the formula to the rest of the cells in column C.
- Voila! You now have a cumulative total of column B in column C!
What are Absolute and Relative Cell References?
Cell references can be absolute or relative, depending on how they behave when copied to other cells. A relative cell reference changes when it is copied or filled to other cells, while an absolute reference remains the same regardless of where it is copied.
An absolute cell reference is specified using the dollar sign ($) symbol. For example, an absolute reference to cell B2 would be written as $B$2.
Here’s an example to illustrate the difference between relative and absolute references. In the following screenshots, you can see two references to the value of cell A1:
- A1 (relative reference)
- $A$1 (absolute reference)
Both references give the same result, which is “20”.
Now, try using the fill handle to copy the formula to the next cell in the column:
You will notice that the reference in cell B2 changed from A1 to A2 to reflect the change in the row number. The reference in cell C2, on the other hand, remains the same.
Explanation of the Formula
The formula in cell C2 is =SUM($B$2:B2). This adds the values from cell B2 to B2, which is just a single cell, so the answer would be 72.34.
In cell C3, the formula becomes =SUM($B$2:B3). This adds the values from cell B2 to B3, which consists of the first two values in the list of daily expenses.
Similarly, in cell C4, the formula becomes =SUM($B$2:B4). This adds the values from cell B2 to B4, which consists of the first three values in the list of daily expenses.
This pattern continues until the last row, where you get the sum of all the values from the first to the last row of the list of daily expenses.
Creating a Dynamic Cumulative Total Column
If you don’t like the idea of copying the formula for the cumulative total every time a new value is added to the list of daily expenses, you can add an IF statement to the formula. This ensures that a cumulative total is calculated only if the corresponding daily expense value is not empty.
For example, for cell C2, the formula could be modified as follows:
=IF(B2<>"",SUM($B$2:B2),"")Then, you can copy this formula throughout the column in advance so that only when a new value is added to column B, the corresponding value in column C will automatically calculate the cumulative total up to that point.
In this tutorial, we have shown you how to calculate and display a dynamic cumulative total for a single column in Google Sheets. It only requires a simple application of the SUM function with a combination of relative and absolute cell references. We hope you found this tutorial helpful.
I hope you found this tutorial useful! By the way, if you want to learn more about the tools and features of Google Sheets, visit Crawlan.com.
