How to Get Running Totals in Google Sheets (Easy Formula)

By

A running total is a useful aggregation tool that helps you see the cumulative sum of a sequence of numbers at any point in the sequence.

It is widely used in a number of applications in both academics and the business world.

In this tutorial, we are going to show you how to add a running totals column to your dataset for a sequence of numbers using the SUM Function

What is a Running Total?

A running total, also known as a cumulative sum is a sequence of partial sums of any given sequence of numbers.

It is updated each time a new number is added to the sequence, by adding the value of the new number to the previous running total.

In this way, you get a feel of the growth in the sequence and also know the final sum at a glance by simply looking at the last total.

In the business world, a running total is widely used to keep track of expenses, revenue as well as inventory or employees’ hours.

How to Get a Running Total in Google Sheets

To explain how to get a running total in Google Sheets, we are going to use the following dataset:

Dataset for running totals

We will demonstrate how to apply a running total to the sequence of numbers in column B (Daily Expenditure) of the above dataset.

Using the SUM Function to Get Running Total in Google Sheets

In this method, we will use the SUM function to add the values of column B’s first row to the value in column B’s current row. So to find the running total at row 9, for instance, the formula would be:

=SUM($B$2:B9)

This will find the sum of all values starting from row 2 all the way to row 9.

Notice we used dollar symbols ($) in the starting cell reference and did not use them in the ending cell reference.

This is because we want the starting cell reference to be an absolute one, while the ending one should be a relative one.

These mixed references will allow you to create a growing total that is anchored to the initial value.

Here are the steps to get a Running total for the Daily Expenditure in column C:

  1. Type the formula: =SUM($B$2:B2) in cell C2.
  2. Press the fill handle for cell C2 (located at the bottom right corner of the cell). This will copy the formula to the rest of the cells in column C.
  3. You should now have a running total of column B in column C!SUM formula for running totals

What are Absolute and Relative Cell References?

Cell references can be absolute or relative based on how they behave when copied to other cells. A relative cell reference changes when it is copied or filled to other cells. An absolute reference, on the other hand, remains the same no matter where it is copied.

An absolute cell reference is specified using the dollar symbol ($). For example, an absolute reference to the cell B2 is written as $B$2.

Here’s an example to demonstrate the difference between absolute and relative references. In the following screenshots, you can see there are two references to the value is cell A1:

  • A1 (a relative reference)
  • $A$1 (an absolute reference)

Both give the same result, which is ‘20’.

Relative cell reference A1

Absolute cell reference A1

 

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 the A1 to A2 to reflect the change in the row number.

Relative cell reference A2

The reference in cell C2, however, remains the same.

Absolute cell reference A2

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.

SUM formula for running total explained

In cell C3, the formula now becomes =SUM($B$2:B3), This adds values from cell B2 to B3, which consist of the first two values in the Daily expenditure list.

SUM formila adjust in the cell below

Similarly, in cell C4, the formula becomes =SUM($B$2:B4), This adds values from cell B2 to B4, which consist of the first three values in the Daily expenditure list.

Running total in cell C4

This goes on to the last row where you get the sum of all values from the first to the last row of the Daily expenditure list.

Final Running total

Creating a Dynamic Running Total Column

If you don’t like the idea of copying the formula for Running total every time a new value is added to the Daily Expenditure list, you could add an IF statement to the formula to ensure that a running total is calculated only if the corresponding daily expenditure value is not blank.

For example, for cell C2, the formula could be changed to:

=IF(B2<>””,SUM($B$2:B2)),””)

Then you could copy this formula to the entire column in advance so that only when a new value is added to column B, the corresponding value in column C automatically calculates the running total up to that point.

Dynamic SUM formula for running total

In this tutorial, we showed you how to calculate and display a dynamic running total for a single column in Google Sheets.

All it requires is a simple application of the SUM function with a combination of both relative and absolute cell references. We hope this tutorial was helpful for you.

I hope you found this tutorial useful!

Other Google Sheets tutorials you may also like:

Popular Posts