Running Total Array Formulas (using the MMULT function)

In this post we’ll look at how to calculate a running total, using a standard method and an array formula method. We’ll cover the topic of matrix multiplication (take a deep breath, it’s going to be ok!) using the MMULT formula, one of the more exotic, and challenging formulas in Google Sheets.

If you like video tutorials, here’s the one on MMULT:

This is a lesson from my latest, Google Sheets course on Advanced Formulas 30 Day Challenge (it’s free!).

Running total dataset

For the examples that follow, we’ll use this dataset:

dataset for running total

We want to add a running total in column C.

Basic Running total

Let’s start off with the most basic method, using a standard (non-Array) formula which we can then copy down our column.

Taking the dataset above, we start our running total in cell C2, with this formula:

= B2

It simply returns our first value, which is our only value at this stage.

For our second value onwards, we can use the same standard formula, simply adding the new value to the running total from the line above:

= B3 + C2

This formula can then be dragged down as far as required to give a running total:

Basic running total formula

If new items are added to the dataset, this formula will need to be dragged down further. Or you could pre-populate the column by dragging all the way to the bottom of your Sheet, and wrapping in an IF statement to hide on the blank rows, like so:

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

The principal disadvantage of this formula is this need to fill out the entire column with a new formula for each row.

Wouldn’t it be nice if we could have a single formula at the top of the column to calculate the running total for us?

But first…

A quick aside on matrix multiplication

It’s been a while since I’ve done matrix multiplication, so I dug out one of my graduate mathematics books: Sets & Groups, A First Course in Algebra by J.A. Green.

Once I got over the initial shock of seeing “Ben Collins 1999” written on the inside cover (yes, I’m really that old!), I flipped to page 192 and reminisced about the good old days… no, I mean I refreshed my mind on the particulars of matrices, particularly their multiplication.

The only definition worth stating here, that pertains to the MMULT function, is this one:

The product AB of matrices A = (aij) and B = (bhk) is defined if and only if the number of columns of A equals the number of rows of B.

In other words if A has shape (m, n) then B must have shape (n, p), for this to work. The resulting matrix will have a shape (m, p).

Each entry in the new matrix is defined by the rule

(i, k) entry of AB = ith row of A * kth column of B

The MMULT formula does this matrix multiplication for us. It takes matrices A and B as arguments, =MMULT(A,B), and outputs the matrix product AB.

Phew, math aside, let’s get back to the more comfortable ground of our Google Sheet.

Array formula running total

Now you know how to do matrix multiplication, let’s use that in an example with the MMULT function.

Let’s strategize before diving headlong into the formulas and note that:

1) A single row matrix, 1-by-X, multiplied by a single column matrix of compatible size, X-by-1, will result in a 1-by-1 matrix, i.e. a single value.

2) Taking this a step further, if the row matrix is {a,b,c} and the column matrix is {x;y;z}, then the product will be ax + by + cz, a single value, which is the sum of the elements multiplied together.

3) So, if we can get the values from our range in column B into a row format and multiply them by a column vector, we’ll be able to add them up!

4) The clever trick is to create a matrix 1 that only has the “correct” values in each row, so for row 1 it should only have the first value, for row 2 it should have the first two, for row 3 the first three etc. I.e. only the current or prior values in each row of our matrix.

5) Matrix 2 simply enables the multiplication to happen, so that the values can be added. So for this a vector of 1 would suffice.

Back to the problem at hand, using the dataset shared at the top of this post, let’s begin by constructing the two matrices for our MMULT function. I recommend restricting the range to B2:B10, to keep calculations manageable whilst we dissect the formula. Once we have a working formula, we can open up the range to B2:B.

Begin with this formula in cell E2:

=ArrayFormula(ROW(B2:B10))

which simply gives us a column vector of the numbers 2 through 10, i.e. {2;3;4;5;6;7;8;9;10}

Add this formula to cell F1:

=ArrayFormula(TRANSPOSE(ROW(B2:B10)))

which simply transposes the column vector above into a row vector of numbers, i.e. {2,3,4,5,6,7,8,9,10}

Now, let’s create a 9-by-9 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, using:

=ArrayFormula(ROW(B2:B10) <= TRANSPOSE(ROW(B2:B10)))

We should now have this output (color coding added):

Row and Column vector matrix

which gives TRUE where the column vector value is less than or equal to the row vector value, and false otherwise.

Multiplying this by the original data range, B2:B10, turns the TRUE values into numbers and the FALSE values into zeros:

=ArrayFormula((ROW(B2:B10) <= TRANSPOSE(ROW(B2:B10)))*B2:B10)

to which we apply a transpose function, to flip the data into the correct orientation

=ArrayFormula(TRANSPOSE((ROW(B2:B10) <= TRANSPOSE(ROW(B2:B10)))*B2:B10))

so that the values from our data range in B2:B10 are now sitting in a 9-by-9, correctly starting from their corresponding row (so value in B2 starts in row 2, value in B3 starts in row 3 etc.), as shown in this image:

Running total matrix 1

So that’s matrix 1 ready for the MMULT formula.

Let’s construct matrix 2. Thankfully it’s a little simpler:

=ArrayFormula(SIGN(B2:B10))

which creates a 9-by-1 matrix, or column vector, where any positive numbers in the range B2:B10 return 1, and blank cells return 0, so the end result is our column vector {1;1;1;1;1;1;1;0;0}

Plug these both into the MMULT function and watch the magic happen:

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

Recall, from matrix multiplication, our 9-by-9 matrix multiplied by our 9-by-1 matrix results in a new 9-by-1 matrix.

Each element in the first row of matrix 1 is multiplied by the column values in matrix 2, and added together, which if I try to illustrate, looks like this:

The first row of matrix 1 is {1,0,0,0,0,0,0,0,0}

The first column of matrix 2 is {1;1;1;1;1;1;1;0;0}

Therefore the first value in the new matrix is:

1*1 + 0*1 + 0*1 + 0*1 + 0*1 + 0*1 + 0*1 + 0*0 + 0*0
= 1 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0
= 1

(You may have noticed that this is a 1-by-9 matrix multiplied with a 9-by-1 matrix, resulting in a 1-by-1 matrix, or simply, a single value.)

Correspondingly, row 2 calculation:

1*1 + 3*1 + 0*1 + 0*1 + 0*1 + 0*1 + 0*1 + 0*0 + 0*0
= 1 + 3 + 0 + 0 + 0 + 0 + 0 + 0 + 0
= 4

Row 3:

1*1 + 3*1 + 9*1 + 0*1 + 0*1 + 0*1 + 0*1 + 0*0 + 0*0
= 1 + 3 + 9 + 0 + 0 + 0 + 0 + 0 + 0
= 13

Etcetera, to our desired output of:

{1;4;13;21;31;82;114;114;114}

So this formula gives the running total.

The final step is to only show it on non-blank rows, which we achieve with standard IF logic.

Here I’ve used this construction

IFERROR(1/0)

to ensure the cells are blank whenever range B2:B10 is blank. 1/0 always results in a #DIV/0! error so the iferror wrapper then leaves the cell blank.

So our formula is now:

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

which can be generalized to the whole of column B, by removing the 10 row reference from B2:B10.

Our formula in its final state is:

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

as shown in the following screenshot:

Array formula running total

If new rows of data are added in columns A and B, the running total column will update automatically.

Can I see an example worksheet?

Yes, here you go.

Bonus: conditional array formula running total

It’s possible to create a single array formula that creates separate running totals for each unique item in Column A, as shown in this image:

Conditional running total

The formula for this is a complete beast (and I’m not saying this is the only, or best, way of achieving this):

=ArrayFormula(query(query(if({transpose(unique(indirect("A6:A"&counta($A$6:$A)+1)))}=$A$6:$A,mmult(transpose((row($B$6:$B)<=transpose(row($B$6:$B)))*$B$6:$B),if({transpose(unique(indirect("A6:A"&counta($A$6:$A)+1)))}=$A$6:$A,1,0)),0),"select "&left(concatenate(transpose("Col"&row(indirect("1:"&countunique($A$6:$A)))&"+")),countunique($A$6:$A)*5-1)&" label "&left(concatenate(transpose("Col"&row(indirect("1:"&countunique($A$6:$A)))&"+")),countunique($A$6:$A)*5-1)&" ''",0),"select * limit "&counta(A6:A)))

Needless to say, I’m not going to break that out line by line, at least not today.

If you’re interested, the best way to approach it is to peel back the layers (like an onion) until you reach the inner most formula, and then build it back up.

Good luck!

7 thoughts on “Running Total Array Formulas (using the MMULT function)”

  1. Wow. I got lost here pretty quickly, after ‘Array formula running total’. Why are you multiplying numbers (and why ROW numbers, at that?) when you want to be adding numbers? Could you explain how you decided that this problem needed a matrix multiplication solution in the first place? Thanks.

    1. Hey Gary,

      I’ve added some more notes to the start of that section, which will hopefully give some insight. The key is knowing the inner workings of matrix multiplication, specifically that if the row matrix is {a,b,c} and the column matrix is {x;y;z}, then the product will be ax + by + cz, a single value, which is the sum of the elements multiplied together.

      So basically we create rows containing the values from B up to the current row, and multiply each row against a column of 1’s which then adds up those values for each row (to give the cumulative total).

      Hope that helps.

      Cheers,
      Ben

        1. I hadn’t done matrix multiplication for decades, and I managed to skim read over the bit were you said after the multiplication the numbers are added together 😳, which explains why you’d use this formula for a running total. The video thankfully makes that point clear.

          1. Great! Thanks for your original question too. I had made a leap there without explaining why, so you’ve helped improve the post 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *