If you are maintaining a large data set such as daily expenses in Google Sheets, you might want to know the sum of expenses of a few consecutive days at the given time. For example, you want to know the total of the first 6 days.

Using the summation formula at the end of the column will only give you the total sum of expenses for the whole month. In this case, you need to calculate the running total in Google Sheets while dealing with large datasets.

This article explains all the possible methods to do running total in Google Sheets.

What is Google Sheets Running Total?

Google Sheets Running total is a cumulative sum of a sequence of numbers. Unlike the formula used to calculate the sum of numbers, running total gives the sum of all or partial numbers at any given time. It is updated each time a new number is added to the sequence. It is calculated by adding the value in sequence to the previous total.

Running total is broadly used to calculate expenses, sales, revenue, etc.

Just like there are many ways to calculate subtotal in Google Sheets, you can learn how to do a running total in Google Sheets by the following methods:

1. Basic sum function (no Array)
2. Basic sum function (with Array)
3. Dynamic Running Total
4. Array Formula with MMULT function

Let’s explore each one by one.

Basic Running Total (no Array)

This method is similar to the basic subtraction function in Google Sheets.

The following dataset will be used for explanation. Column C will be used to calculate the running total.

1. To get the first value in column C, just add the “=B2” formula in cell C2 and hit the Enter key. It will return the same value that is in cell B2.

2. For the next cell i.e., C3, enter the basic sum formula i.e., “=B3+C2”.

3. To obtain the running total in Google Sheets at any next point in the sequence, simply drag the basic sum formula down to the cell until where you require it.

Basic Running Total (with Array)

This is similar to the basic calculation of Google Sheets running total as explained above. However:

• It uses both, relative and absolute cell references.
• The formula used contains a range of cells and not just two cells.

Let’s see how to do a running total in Google Sheets using this method.

Formula

Considering the above example, to get a running total in C2, our formula will become:

=SUM($B$2:B2) 

It will return the same value as in B2.

To get a running total in C4, it will become:

= SUM($B$2:B4)

It will add the values from B2 to B4.

Explanation

If you notice that there is a colon in the formula as well as a $ sign with one cell reference.

The colon signifies the range of cells. For example, the range is B2 to B4 i.e., 3 cells.

The $ sign differentiates an absolute cell reference from the relative cell reference. These two cell references behave differently when copied.

Relative Cell Reference

= A2 

The cell reference without a $ sign changes when copied to another corresponding cell. For example, A2 will change to B2, A3 to B3, and so on.

Copying the relative cell reference to the below cells.

Absolute Cell Reference

=$A$2

The cell reference with $ signs remains the same when copied to any cell in the google sheet. For example, $A$2 when copied to B3, and B4, returned the same value as it was in A2.

However, a relative cell reference would have copied the values of A3 and A4 to B3 and B4 respectively.

Copying the absolute cell reference to the below cells.

Generic formula

So, the generic formula will become:

=SUM (Absolute Cell Reference: Relative Cell Reference)

For the dataset shared below, copy the formula “=SUM($B$2:B2)” to the cells C2 and onwards to obtain the running total in Google Sheets.

Please note that the absolute cell reference remains the same, while the relative cell reference changes to return the cumulative sum of the numbers in the sequence at the given point in the Running Total column.

Dynamic Running Total

This method simply refers to the concept of prepopulating the google sheet column with a running total formula. So, whenever a new value is added to the sequence, the new running total is calculated automatically without having you copy the basic sum formula or the SUM of range of cells formula to the new cell in the Running Total column in the google sheet.

Let’s see how to implement the dynamic running total method using each of the running total formulas explained above.

Dynamic running total using the basic sum (no Array) formula

Add the formula basic sum formula (no-Array) with an “IF” statement combined with the “ISBLANK” condition.

So, in the example above, instead of inserting “=B3+C2” in the cell C3, add the formula:

=IF(ISBLANK(B3), ““, B3+C2)

It will first check if there is a value in B3. If not, it will leave C3 blank. Otherwise, it will insert the sum obtained by B3+C2 into the cell C3.

Now, you can drag this formula down to the rest of the rows that have no data at the moment.

The downside of calculating the running total using the basic formula is that you have to copy the formula manually for each new value in the sequence.

Dynamic running total using the basic sum (with Array) formula

This dynamic running total formula in Google Sheets is obtained by using a conditional IF statement. For example, with reference to the same dataset shared above, the formula will be:

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

Now the running total value in C2 will be displayed only if B2 is not blank. If B2 is blank, then C2 will remain blank.

Array formula running total with MMULT function

The downside of all the methods explained above is:  

• You have to copy the running total formula in the new cell whenever a new value is added to the sequence, or
• You have to make it dynamic by copying the formula to the selected number of cells of the column, or
• You have to use another (helper) column in your formula such as “=B3+C2”.
• Also, you cannot generalize the implementation of these formulas to the entire column in such a way that whenever a new value is added to the sequence irrespective of the row count, the Running Total value is also added in the next column.

Array formula with MMULT function is used if you want to calculate the running total in Google Sheets in such a way that:

1. The formula can be implemented in the entire column
2. There is no need to mention the specific range of rows
3. There is no need to use a helper column

An Array formula with MMULT function for running total can be generalized for the entire column containing the sequence of numbers in the following way:

=ArrayFormula(IF(B2:B,MMULT(TRANSPOSE((ROW(B2:B)<=TRANSPOSE(ROW(B2:B)))*B2:B),SIGN(B2:B)), IFERROR(1/0)))

Explanation

The running total is achieved by the multiplication of two matrices. Before we dive into matrix multiplication, let’s recall the matrix multiplication rule.

The number of columns in matrix A should be equal to the number of rows in matrix B such that A is a matrix of order (m x  n), and B is a matrix of order (n x p), then the resultant matrix will be of the order (m x p).

Follow the steps mentioned below to learn how the running total in Google Sheets is calculated by using the Array MMULT formula.

Let’s consider the following dataset. We will use B2:B6. The cumulative sum will be calculated in column C (Running Total).

Use the rest of the space in the google sheet to perform the following steps.

Obtaining the first Matrix for multiplication

Step 1

Write the column B row numbers in any cell using the array row formula “=ArrayFormula(Row (B2:B6))” and hit Enter. The formula will be displayed in the formula bar as well.

Once you hit Enter, the row numbers of the selected cells range will appear in a sequence.

For B2:B6, the row numbers “2,3,4,5,6” will be copied.

Step 2

Take transpose of the array that is achieved in Step 1. To do so, enter the array transpose formula in the first cell of Matrix 1 as demonstrated below.

=ArrayFormula (Transpose(Row(B2:B6)))

This formula will give a row vector {2,3,4,5,6}.

Once you hit the Enter key after adding the array transpose formula, the column will be converted to a row.

Now paste the Array row formula in a different column to get the row numbers of column B as you did before (in Step 1).

=ArrayFormula(Row(B2:B6))

It will give the column vector {2;3;4;5;6}.

Referring to the image below, there is a row vector {2,3,4,5,6} beginning from E2, and a column vector {2;3;4;5;6} beginning from D3.

Step 3

Compare the values of the column vector against the row vector such that if the value in the vector column is smaller than the value in the row vector, the following formula returns True.

=ArrayFormula(row(B2:B6) <= transpose (row(B2:B6)))

You can apply the conditional formatting to view the cells with the value “True”.

Step 4

Multiply compared results with the dataset in B2:B6 for which the running total is being calculated.

1. Enter the following formula in the first cell of the TRUE/FALSE data. In the example below, it’s E3.

=ArrayFormula((row(B2:B6)<=transpose(row(B2:B6)))*B2:B6)

Once you hit Enter, all the TRUE values will be changed to the values in the corresponding cell number. Any TRUE value in the first row will be changed to the first value of the sequence B2:B6.

Similarly, all the TRUEs of the second row will be changed to the next value in the sequence. All the FALSE values will be changed to zero (0). Refer to the image shared below.

In short, a matrix of order (5 x 5) will be obtained against the sequence in B2:B6.

Step 5

Take the transpose of the matrix by the formula shared below.

=ArrayFormula(Transpose((row(B2:B6)<=transpose(row(B2:B6)))*B2:B6))

The resultant matrix will be the first matrix for matrix multiplication.

Obtaining the second matrix for multiplication

Consider the same dataset i.e. B2:B6 that is used for getting the running total in Google Sheets.

Paste the following Array formula in any other cell on the google sheet. For example, in the image below, E9 is used.

=ArrayFormula(Sign(B2:B6)) 

This formula will return a column vector with either 1’s or 0’s.

• 1 will be returned if the corresponding referenced cell is not empty.
• 0 will be returned if the referenced cell is empty.

For B2:B6 in the following example, a matrix of (5 X 1) has “1” for all the entries because all the cells in B2:B6 have a value.

Multiplication of the two matrices

To get the running total in Google Sheets by using the array multiplication formula, apply the following formula to C2.

=ArrayFormula(MMULT((Transpose((row(B2:B6)<=transpose(row(B2:B6)))*B2:B6)),Sign(B2:B6)))

It’s a simple matrix multiplication formula.

=ArrayFormula (MMULT(Matrix 1), (Matrix 2))

If you recall, you obtained the two matrices by the following array formulas:

Matrix 1: =ArrayFormula(Transpose((row(B2:B6)<=transpose(row(B2:B6)))*B2:B6))

Matrix 2: =ArrayFormula(Sign(B2:B6))

So, the formulas of matrix 1, and matrix 2 are used in the Array Matrix Multiplication formula to get:

=ArrayFormula(MMULT((Transpose((row(B2:B6)<=transpose(row(B2:B6)))*B2:B6)),Sign(B2:B6)))

Dynamic Running Total using Array Matrix Multiplication

To get the dynamic running total such that whenever a new value is added to the sequence in column B, the running total in column C is also updated, just skip the ending row number in the formula to get

=ArrayFormula(MMULT((Transpose((row(B2:B)<=transpose(row(B2:B)))*B2:B)),Sign(B2:B)))

Now the array matrix multiplication formula is applied to the entire column and the running total will be calculated dynamically whenever the sequence number is updated.

Bonus

If you notice, then you will see that for the blank cells in column B, the Running Total returns the same value as it was calculated for the previous value in the sequence.

So, to keep the formatting of your google sheet clean and to avoid any confusion, the running total cell should be blank when the corresponding cell in column B is blank. Here you can use the IF statement with an IFERROR wrapper.

So, the Google Sheets running total formula will become

=ArrayFormula(IF(B2:B,MMULT(TRANSPOSE((ROW(B2:B)<=TRANSPOSE(ROW(B2:B)))*B2:B),SIGN(B2:B)), IFERROR(1/0)))

Since 1/0 will always give #DIV/0! Error, so using an IFERROR wrapper with an IF statement will leave the cell blank when there is no data.

Final Thoughts

Having explored all the above-explained methods of getting Google Sheets running total, one might question “which method is best to use when?”

To keep it simple, you can use the basic dynamic running total methods for predictable small datasets. For example, if you are doing a weekly total of your expenses.

For increasing datasets, the array matrix multiplication formula will best serve your needs. For example, if you are maintaining a Google Sheet for yearly chargebacks.

Related Reading:

• The 3 Google Sheets Wildcards and How to Use Them
• How to Make a Google Sheets Button [Easy Guide]
• Google Sheets Reverse Order: Flip Data in Google Sheets
• How to Randomize a List in Google Sheets (Shuffle Data)
• An Autofill Google Sheets Guide: Fill Down Data
• The Easy Guide to Google Sheets Array Formulas [Step-by-Step]
• How to Calculate Year Over Year Growth