Running Total in Google Sheets

What Is Running Total in Google Sheets?

A running total in Google Sheets is also known as a rolling total. It is described as the sum of a set of numbers, which gets updated each time a new number is added. Calculating the running total in Google Sheets is very important in data analysis, as it helps track the cumulative total when new data is added. Running totals offer comprehensive ideas of how the data changes over time, whether tracking sales data, managing household/business budgets, or even for projects.

Now, we will try to explain everything with a simple example for your understanding. Enter the data for which you must calculate the running total in a column. Here, we enter the daily sales of a store in Column A. Now, note the first cell with data; Here, it is A2. Therefore, in B2, enter the sales of the first day by referencing it.

=A2

Press Enter. Now, B2 is the beginning point of your running total. Next, add the following formula in B3: sum the current value with the previous total.

=B2+A3

Press Enter and drag the formula till B6. The formula adds the value directly above it to the value next to it, thereby updating your running total.

How to Calculate Running Total (Cumlative Sum) in Google Sheets?

There are several different ways to calculate the running total in Google Sheets. Let us look at the most basic method. It is suitable for smaller datasets and can be used to track household budgets and other simple things. Let us look at how to do it step-by-step.

Step 1: Place the household’s daily expenses in Column B.

Step 2: Now, to calculate the running total, in the adjacent column in cell C2, enter =B2 as the first point of the running total.

Step 3: In the next cell C3, use the formula =C2+B3. Now, drag the formula down the column to calculate the dynamic running total.

Calculation using the SUM function

Besides this simple formula, for bigger datasets, you can use the SUM function. It simplifies the calculation without any need for manual calculations where you may go wrong. For example, using the same dataset as above, let us see how to do it step-by-step.

Step 1: In C2, input the following formula =SUM($B$2:B2). This formula calculates the running total from cell B2 to the current cell.

Step 2: Now, drag the formula down from C2 to apply it to all the other rows. This creates a dynamic running total that is automatically updated with new entries.

Examples

Now that you have a basic idea of how to calculate the running total in Google Sheets, let’s look at some examples of different ways to calculate it.

Example #1 – Dynamic Running Total without SUM formula

This method is the basic way to calculate the running total without using the SUM function. The dataset below contains some of a company’s project budget details.

Step 1: We start by calculating our running total in cell C2, using the formula.

=B2

Here, we get the first value, which is the running total at this point.

Step 2: From the second value, we use a standard formula, keeping the new value added to the accumulating running total above.

=B3 + C2

Step 3: This formula is dragged down to the end of the dataset. If new items are added to the dataset, this formula will have to be dragged down further.

Example #2 – Dynamic Running Total with SUM formula

Now, let us look at an example using the SUM Function to Get Running Total in Google Sheets. Below is a dataset containing some sales amounts.

Step 1: We will use the SUM function to add the data in column B’s first row to its data in the current row. Enter the following formula in cell C2.

=SUM($A$2:A2)

Step 2: Now, press Enter. You get the running total for that row. Now, drag the formula to B6.

We have used the dollar symbol ($) for the start of the reference as it should be fixed, and the ending one does not have the symbol as it is dynamic.

Thus, the mixed references allow you to calculate the rowing total with a fixed initial value.

You should now have a running total of column A in column B.

Example #3 – Using the MMULT Function

Calculating the running total using the MMULT function is a bit challenging, but let’s look at some simple examples for knowledge purposes. We have the following values in Column A.

Step 1: To find the running total using MMULT in Google Sheets, in cell B1, enter the following formula:

First, let us understand matrix calculations as shown below.

a) A single row matrix, when multiplied by a single column matrix of similar size,  results in a single value or 1*1 matrix.

b) For example, if the row matrix is {1,2,3} and the column matrix is {4,5,6}, then the product will be 1*4 + 2*5 + 3*6, which is a single value, and the sum of the elements multiplied.

c) In this example, we get the values from Column A into a row format and then multiply them using a column vector and add them to find the running total.

Step 1: We begin by using the formula

=ArrayFormula(ROW(A1:A5))

We get a column vector of the numbers 1 to 5, i.e., {1;2;3;4;5}.

Step 2: Add a TRANSPOSE function to the above formula to transpose the column into a row vector, {1,2,3,4,5}

=ArrayFormula(TRANSPOSE(ROW(A1:A5)))

Step 3: Now, let’s create a 5*5 matrix by comparing these two vectors and recording whether the value from the column vector is less than or equal to the value from the row vector.

=ArrayFormula(ROW(A1:A5) <= TRANSPOSE(ROW(A1:A5)))

Step 4: Multiplying this matrix with the original range A1:A5 turns TRUE into numbers and FALSE into zeros.

Step 5: We use TRANSPOSE for the correct orientation.

=ARRAYFORMULA(TRANSPOSE((ROW(A1:A5) <= TRANSPOSE(ROW(A1:A5)))*A1:A5))

Step 6: Now, let us construct matrix 2. It creates a 5*1 matrix where positive numbers in the range A1:A5 return 1, and blank cells return 0. Hence, we get the column vector {1;1;1;1;1;1;1;0;0}

Finally, combine the two formulas and watch the result.

=ArrayFormula(MMULT(TRANSPOSE((ROW(B2:B10) <= TRANSPOSE(ROW(B2:B10)))*B2:B10),SIGN(B2:B10)))

Example #4 – Using ARRAYFORMULA

As most of us know, ARRAYFORMULA in Google Sheets helps make calculations for an entire column simultaneously. It calculates the running totals for an entire column without the need to drag the formula across the column. It also uses an array in functions where there is no array.

Step 1: Let us enter some numbers in Column A.

Step 2: To set up the running total, enter the following formula in cell B1, which we will build upon.

=ARRAYFORMULA(IF(A1:A5<>””, SUMIF(ROW(A1:A5), “<=”&ROW(A1:A5), A1:A5), “”))

We will break down the formula for you. In the SUMIF function, the ROW range argument returns the row number for each cell in the range in an array.

The ROW function returns an array because it is inside ARRAYFORMULA. Hence, it takes a range and returns a range.

• IF(A1:A5<>””, …) just checks that the cells are not empty in that range.
• SUMIF(ROW(A1:A5), “<=”&ROW(A1:A5), A1:A5) – Here, the ROW function returns {1, 2, 3, 4, 5}.
• “<=”&ROW(A1:A5) checks for all rows less than or equal to the current row and then sums the corresponding values in column A.

Thus, with this slightly complex formula, you can use ARRAYFORMULA for the running total.

Important Things to Note

1. For best results, always calculate the running total in a separate column immediately next to the dataset. This makes it easier to update the data without messing up the calculations.
2. You can use charts to visualize the running total which will provide you with meaningful insights into the data.
3. If there are empty cells in the dataset, you might get unexpected results. To avoid this, you can fill the blank cells with zeros or use functions like IF or IFERROR to handle these blank cells.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the Running Total in Google Sheets, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is Running Total in Google Sheets. We learn syntax & how to use Running Total in Google Sheets , examples, working template. You can learn more from the following articles.

Split in Google Sheets

Types of Charts in Google Sheets

FLOOR.MATH function in Google Sheets