In this post, you are going to see all the ways you can get a running total in Google Sheets.

The running total is the addition of a sequence of numbers such that every new number introduced to the sequence is added to the previous total. The running total is the cumulative addition of a series of numbers.

The running total concept has found use in various areas of business because it allows for easy identification of the total at any point in time.

For example, if you have a record of daily sales and you want to know the total sales year to date, you can easily find it with the running total.

Calculating the running total is a simple process, and Google Sheets has made it even easier.

In this post, you will learn the following 5 ways that you can use to get the running total.

- The addition
`+`operator. - The
**SUM**function. - The
**ARRAYFORMULA**function. - Custom Apps Script function.
- Pivot Table Calculated Field.

Get your copy of the example workbook with the above link to follow along!

## Get a Running Total with the Addition Operator

This method doesn’t require you to use a function to get the running total. You only have to use the addition operator `+` to add up the cells you need.

Follow these steps to get the running total for this sample data using the addition operator.

- Enter
`=D2`

into cell**E2**. This will copy the contents in cell**D2**to cell**E2**.

- Enter the formula
`=D3+E2`

into cell**E3**.

The formula will cumulate the total for the **Natural Deodorant** product and the amount of the **Zero Waste Shampoo Bars** product. This is the running total for the second row.

- Drag down the fill handle to copy the formula to the empty cells.

When copying the formula into empty cells, you can run into a situation where you start to get a similar figure as the previous running total.

In this case, the figures in the **Amount** ends at row 11, but the formula has been copied further into row 15. Since the cells **D12** to **D15**, the formula returns the same number.

In a large dataset where blank cells are present anywhere in-between, issues like that can be difficult to spot and correct.

You can wrap the formula in an **IF** function to prevent this problem.

`= IF ( ISBLANK ( D3 ), "-", D3+E2 )`

Copy and paste this formula in cell **E3** and drag down the fill handle.

The **IF** function takes a logical expression and the values to return when the logical statement is true or false. In this syntax, the **ISBLANK** function is used to check if any cells in the **Amount** column are blank.

If the **ISBLANK** logical expression returns true, then the function will return a dash `-` character. If the logical expression is false, the function will return the running total operation.

The syntax allows you to format empty cells so that when you add new entries to the **Amount** column, the running total will automatically be calculated.

## Get a Running Total with the SUM Function

In place of using the addition operator, you can use the **SUM** function to get the running total.

`= SUM ( value1, [value2, …] )`

The **SUM** function can add individual values or values from a cell range. It can take an arbitrary number of value arguments.

`= SUM ( $D$2:D2 )`

Copy the above formula into cell **E2** to get the running total of the sample data and then copy and paste it down to the last row.

In the syntax, cell **D2** has been fixed so that the summation range starts from cell **D2 **every time the formula is copied to a new row and new values are added.

`= IF ( ISBLANK ( D2 ), "-", SUM ( $D$2:D2 ) )`

You can troubleshoot the formula using the **IF** function like in the previous method to avoid showing a total when there is no data in the row.

## Get a Running Total with the ARRAYFORMULA Function

More often than not, the first two methods will cover all your running total calculations needs.

The benefit of this method over the previous methods discussed is that you won’t have to copy down the formula.

This method will use a combination of the **ARRAYFORMULA**, **SUMIF**, and **ROW** functions.

`= ARRAYFORMULA ( array_formula )`

The **ARRAYFORMULA** function allows operations to return results in multiple rows and columns. It also allows the use of an array in functions that would normally not take an array. This attribute is crucial to our objective here.

Next up is the **SUMIF** function.

`= SUMIF ( range, criterion, [sum_range] )`

The **SUMIF** function returns the aggregate of a range based on a specific criteria.

`= ROW ( [cell_reference] )`

The **ROW** function returns the row number of a cell.

`= ARRAYFORMULA ( SUMIF ( ROW ( D2:D11 ), "<=" & ROW ( D2:D11 ), D2:D11 ) )`

To find the running totals copy and paste the above formula syntax into cell E2.

The benefit of using this method is that you don’t have to drag it down to copy the formula to other cells. You just enter the formula at the top of the column to calculate the running totals for all the rows.

Here’s a break down of how the formula works.

In the **SUMIF** function, the **ROW** in the **range** argument returns the row number for each of the cells in the range in an array. In this example, the ROW function returns `{2, 3, 4, 5, 6, 7, 8, 9, 10, 11}`

.

The **ROW** function isn’t an array formula. But because it is inside an **ARRAYFORMULA**, it can take a range argument, and will also return the result in an array format. In this case, it returns the list of the row numbers in 11 rows.

The **criterion** argument of the **SUMIF** function doesn’t work with ranges. Because of this, the **ROW** function in the **criterion** argument behaves a little differently than the **ROW** function in the **range** argument.

The **criterion** argument doesn’t return an array, instead, it returns the row numbers of each cell in the range. The range is just used to specify which cell the operation should start and where to stop.

For instance, when the **criterion** argument calculates, the **ROW** function will return the number 2 for cell **D2 **and checks the **range** argument for rows that have numbers less than or equal to 2.

`= ARRAYFORMULA ( SUMIF ( ROW ( D2:D11 ), "<=" & ROW ( D2 ), D2:D11 ) )`

This is how the syntax will run when testing for cell **D2**.

After completing the operation for row **D2**, the syntax goes to the second cell in the range. In this case **D3**. For this, it will return 3 for cell **D3**.

`= ARRAYFORMULA ( SUMIF ( ROW ( D2:D11 ), "<=" & ROW ( D3 ), D2:D11 ) )`

This is how the syntax will run for cell **D3**.

When the **criterion** portion runs, it checks to find the cells in the **range** that meet the condition. Then the **sum_range** portion adds cells that meet the condition.

When the **criterion** argument is less than or equal to 2, the **sum_range** argument will add the value in cell **D2** because only that cell in the **range** argument meets that condition.

When the criterion argument is less than equal to 3, then the **sum_range** argument will add up values in cells **D3** and **D2** because those are the cells that meet the criteria in the **range** argument.

The syntax continues to run this loop until it reaches the final cell in the operation.

## Get a Running Total with a Custom Apps Script Function

Google spreadsheet provides a code editor that you can use to create custom functions. With an apps script, you can create functions that will automate repetitive tasks, and increase your efficiency.

To open the code editor window, go to the **Extensions** menu and click on **Apps Script**.

```
function RUNNINGTOTAL(myValues) {
var selectedRows = myValues.length;
var selectedColumns = myValues[0].length;
var resultsArray = [];
var runningTotal = 0;
for (var i = 0; i < selectedRows; i++) {
var rowData = [];
for (var j = 0; j < selectedColumns; j++) {
runningTotal = runningTotal + myValues[i][j];
rowData.push(runningTotal);
}
resultsArray.push(rowData);
}
return resultsArray;
}
```

Copy and paste the above code syntax into the script editor.

**Save** the script and grant all the permission necessary to run the script in your spreadsheet. Then, go to your spreadsheet and refresh it.

You will now be able to use the RUNNINGTOTAL custom function in your spreadsheet.

`= RUNNINGTOTAL ( D2:D11 )`

The script creates a custom array formula called **RUNNINGTOTAL**. The function gets the values in the selected range and returns the running totals.

Unlike other methods, this custom function provides everything you need for calculating a running total in one place.

With **RUNNINGTOTAL**, you don’t have to combine two or more functions to get the running total.

`= ARRAYFORMULA ( IF ( ISBLANK ( D2:D ), "-", RUNNINGTOTAL (D2:D) ) )`

You can even go one step further with **RUNNINGTOTAL** by using the **ARRAYFORMULA**, **IF**, and **ISBLANK** functions to troubleshoot the formula.

With this syntax, you can format all of the cells in the **Running Total** column. The running total for every new entry is automatically calculated.

## Get a Running Total with a Pivot Table Calculated Field

Pivot tables have features that help you filter, group, and sort fields that help you easily explore and identify various trends in your data.

What makes a pivot table different from regular tables is the kind of summaries you can generate.

Creating a pivot table in the spreadsheet is very easy. Follow these steps.

- Select the range of data with which you want to work in the pivot table.
- Go to the
**Insert**menu and click on**Pivot Table**. The pivot table dialogue box that opens will pick up the already selected range. - Select the
**Existing sheet**radio button to insert the pivot table into the existing sheet. - Select a cell and import the pivot table.

When the pivot table is inserted on the sheet, drag the **Order Date** field to the **Rows** section.

In the **Values** section, click on **Add** and select **Calculated Field**.

In the **Calculated Field 1** box, under **Summarise by**, select **Custom**.

`=SUMIFS($D:$D,$B:$B,"<="&'Order Date')`

Delete the content inside the formula bar and paste the above formula syntax. The syntax uses the **SUMIFS** function to calculate the running total.

`= SUMIFS ( sum_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …] )`

The **SUMIFS** function is similar to **SUMIF**. However, **SUMIF** takes only one criteria argument, whereas **SUMIFS** can return the sum of a range based on multiple criteria arguments.

The syntax used to arrive at the running totals adds the **Amount** column if the values in column B are less than and equal to individual **Order Date** values.

As the syntax goes through each **Order Date** value, it adds up all corresponding individual Amounts values that are less than or equal to a specific **Order Date** value.

For the syntax to work, the ranges have been locked in absolute references using the dollar sign, and the **Order Date** field needs to be sorted in ascending order.

You can change the default column header from **Calculated field 1** by double-clicking on the cell.

## Conclusions

You will most likely need to use only the addition operator or SUM function methods to calculate your running totals.

The other methods nevertheless will ensure that you cover any scenario where those two methods may not suffice.

Using an **ARRAYFORMULA** it is possible to return all the running total values from a single formula.

The custom apps script function might provide the best outlet for finding the running total. It’s a much better alternative as it will return all the running total calculations from a singe range input.

If you first need to summarize your data you can do this with a pivot table and calculate the running total inside the pivot table.

What other methods for finding the running totals do you know? Let us know in the comments section below!

## 0 Comments