In this article, we will dive into the coding of an array formula for a conditional running total (also known as conditional cumulative sum) in Google Sheets. Not only that, but we will also explore how to incorporate a group-wise running total using this formula.
Traditionally, when it came to the array formula running total (CUSUM), we were limited to using functions like SUMIF or MMULT in Google Sheets. However, Excel provided the flexibility of using MMULT. But now, with emerging solutions, Lambda functions offer additional possibilities for creating a conditional running total array formula in Google Sheets.
Group-Wise Running Total Array Formula in Google Sheets – MMULT
Key Feature: Works with both sorted and unsorted data.
Let’s start with a sample dataset in cells A2:B, with headers in A2:B2. Before we proceed, you can replicate my sample sheet by clicking here.
The conditional CUSUM formula will be created in cell C3, specifically in the column ‘Separate Running Total for Each Group’. In the dataset, we have three groups or unique values: Jan, Feb, and Mar. It’s worth noting that even if the group column A is sorted differently, my MMULT formula will still work.
The formula we will be using is:
=ARRAYFORMULA(IF(LEN(A3:A), MMULT( N(ROW(A3:A)>=TRANSPOSE(ROW(A3:A)))*N(A3:A=TRANSPOSE(A3:A)), N(B3:B) ),))Let’s break down the formula:
- The first two parts form matrix 1, and the third part forms matrix 2.
- The conditions are applied in matrix 1, which is crucial for obtaining the conditional running total in Google Sheets.
Part 1 (the first condition in the running total formula):
N(ROW(A3:A)>=TRANSPOSE(ROW(A3:A)))This formula generates a matrix that checks whether each value in the vertical sequence numbers is equal to its transposed value and returns TRUE or FALSE. The function N converts TRUE to 1 and FALSE to 0.
Part 2 (the second condition in the running total formula):
N(A3:A=TRANSPOSE(A3:A))This formula tests whether the values in A3:A are equal to the transposed values and returns TRUE or FALSE. The function N converts these Boolean values to 1 or 0.
Part 1 multiplied by Part 2 is equal to matrix 1 in the MMULT function.
Part 3:
N(B3:B)Matrix 2 is the range B3:B itself. The function N converts blank cells to 0 to avoid errors in MMULT.
The entire formula is wrapped in an IF and LEN combination. It checks whether the length of the corresponding cell in column A is greater than 0. If true, it calculates the MMULT result; otherwise, it returns an empty result.
That concludes the explanation of the group-wise running total, a type of conditional running total in Google Sheets.
Group-Wise Running Total Array Formula in Google Sheets – BYROW
Key Feature: Works with both sorted and unsorted data.
The MMULT formula we discussed earlier might lead to performance issues with large datasets. Here’s an alternative solution for a group-wise running total in Google Sheets using the BYROW function.
Insert the following formula in cell C3, which will expand down:
=BYROW(A3:A, LAMBDA(r, IF(r="", ,SUM(FILTER(B3:B, A3:A=r, ROW(A3:A) <= ROW(r)))))) Let’s break down the formula:
FILTER(B3:B, A3:A=r, ROW(A3:A) <= ROW(r)): The FILTER function filters B3:B based on the condition that A3:A is equal to the current value (r) and the row number of A3:A is less than or equal to the row number of r.- The BYROW function helps iterate through each row in A3:A, applying the Lambda function for each element.
IF(r="", ,SUM(FILTER(B3:B, A3:A=r, ROW(A3:A) <= ROW(r)))): The IF function returns the sum of the filtered values in each row if r is not blank.
Conditional Running Total Array Formula in Google Sheets – MMULT
Now let’s move on to a new sample dataset in the range A2:B. If we insert the standard running total array formula in cell C3 without any conditions, it would return an incorrect cumulative sum (CUSUM).
The formula we will be using is:
=ARRAYFORMULA(IF(LEN(A3:A), MMULT( IF(ROW(B3:B)>=TRANSPOSE(ROW(B3:B))=TRUE, 1, 0), N(B3:B) ),))This formula is similar to the one we discussed earlier. The only difference is that we want to exclude the “Total” rows and the account head row (2. Administrative Cost) from being considered in the running sum.
To exclude the “Total” and subheading rows from the output, we need to make two modifications to the formula:
Modification 1 (to exclude Total and Subheading rows from the output):
Replace:
IF(LEN(A3:A)with:
IF((A3:A<>"Total")*(NOT(ISNUMBER(LEFT(A3:A,1)*1)))This modification checks if A3:A doesn’t contain the string “total” and the first character in any string is not a number. Rows that have a subheading, which we want to exclude, will have a number as the first character.
Modification 2 (to exclude “Total” row values in the cumulative sum):
Replace matrix 2, which is:
N(B3:B)with:
IF(A3:A<>"Total", N(B3:B), 0)Finally, here is the conditional running total array formula to use in cell C3:
=ARRAYFORMULA(IF((A3:A<>"Total")*(NOT(ISNUMBER(LEFT(A3:A,1)*1))), MMULT( IF(ROW(B3:B)>=TRANSPOSE(ROW(B3:B))=TRUE, 1, 0), IF(A3:A<>"Total", N(B3:B), 0) ),))Conditional Running Total Array Formula in Google Sheets – SCAN
Here as well, the purpose of using this alternative to MMULT is to improve performance.
You can enter the following formula in cell C3:
=ARRAYFORMULA(IF((A3:A="Total")+(B3:B=""), ,SCAN(0, A3:A, LAMBDA(a, v, IF(v="Total", a, OFFSET(v, 0,1 )+a)))))This formula utilizes the SCAN function in Google Sheets for iterative calculations. Here’s the explanation:
SCAN(0, A3:A, LAMBDA(a, v, …): The SCAN function iterates over the values in the range A3:A, applying the Lambda function for each element. The Lambda function takes two parameters: a (accumulator) and v (current value).IF(v="Total", a, OFFSET(v, 0, 1)+a): This is the core logic of the Lambda function. It checks if the current row value (v) is equal to “Total”. If true, it returns the accumulator (a), effectively skipping the “Total” row. If false, it adds the current value (v) in the next column (OFFSET(v, 0, 1)) to the accumulator (a).
The entire formula effectively calculates a running total, excluding rows where the value in column A is “Total”.
The SCAN function returns values in all rows in the array, even though it doesn’t use the “Total” row values for calculations. Therefore, we have wrapped the formula with an IF logical test to exclude values from those rows.
That concludes our explanation of the conditional running total array formulas in Google Sheets.
Resources
