Some of our readers may already have the formula to return the running total by category in Google Sheets. Then what brought you here?

I know the reason! Your formula stopped working after a specific number of rows.

If that is the case, you were using the MMULT-based formula I posted earlier, and here is that tutorial – Array Formula for Conditional Running Total in Google Sheets.

It may cause an error/stop working when your data grows and possibly after 3000 rows.

I have a different formula for running total by category in Google Sheets which may work even after 3000 rows. But it might affect the performance of your Sheet.

It uses SUMIF to return the cumulative total by category in Google Sheets.

In this example (screenshot # 1), I’ve used the following MMULT formula in cell C2.

=ArrayFormula(MMULT(N(ROW(A2:A9)>=TRANSPOSE(ROW(A2:A9)))*N(A2:A9=TRANSPOSE(A2:A9)),N(B2:B9)))

It will work flawlessly since the data is small and uses closed ranges A2:A9 and B2:B9 instead of open ranges A2:A and B2:B.

Let’s code an alternative to this MMULT below, which would work in a large dataset.

Running Total by Category Using SUMIF in Google Sheets

Do you want me to code the formula step by step or start with the coded formula itself?

I think both will do. So, let’s follow the latter, and here is the formula to use in cell C2 as per the example above.

=ArrayFormula(if(len(A2:A),sumif(row(A2:A), "<="&row(A2:A), B2:B)-sumif(vlookup(A2:A,{filter(unique(A2:A),unique(A2:A)<>""),sequence(counta(unique(A2:A)))},2,0), "<"&vlookup(A2:A,{filter(unique(A2:A),unique(A2:A)<>""),sequence(counta(unique(A2:A)))},2,0), B2:B),))

I have used the following logic in this SUMIF-based formula to return the running total by category in Google Sheets.

Logic:

1. The vivid red highlighted Part 1 returns the usual running total (CUSUM). Please check column D in screenshot # 2 under the Formula Explanation below.
2. The vivid green cyan highlighted Part 2 returns the category-wise CUSUM as a whole and assigns that to each row (please refer to the notes below and column E in screenshot # 2 below).
3. Part 1 – Part 2 = Running Total by Category (refer to column F in screenshot # 2 below).

Notes:-

• The Part 2 formula should return the category-wise CUSUM as a whole skipping the rows containing the first category. You won’t understand it without seeing an example. Please check column E and the yellow boxes/arrow marks in screenshot # 2 below.
• When you test the vivid red and vivid green cyan parts in the above formula in cells D2 and E2 as per the below image, start them with =ArrayFormula(

Formula Explanation – Running Total (Cumulative Sum) by Category

Time to go deep into the formula parts.

Step 1 – CUSUM (Part 1 formula)

I’ve already covered it here – Normal and Array-Based Running Total Formula in Google Sheets.

Step 2 – Category-Wise CUSUM as a Whole (Part 2 formula)

The Part 2 formula plays a vital role in our master formula that returns the running total by category in Google Sheets.

So here is the break-up of it.

If we assign sequential numbers to each category, I mean 1 for “P100,” 2 for “A151,” and 3 for “Q101,” then it’s very easy to get the category-wise CUSUM as a whole as required above (column E in screenshot # 2).

Please check columns D and E in the image below.

I’ve opted for the below method to return the D2:D numbers.

Step 1:

={filter(unique(A2:A),unique(A2:A)<>""),sequence(counta(unique(A2:A)))}

Which will return the below table.

I used this as the range in a Vlookup and the search keys are A2:A.

=ArrayFormula(vlookup(A2:A,{filter(unique(A2:A),unique(A2:A)<>""),sequence(counta(unique(A2:A)))},2,0))

This Vlookup in cell D2 (screenshot # 3) generates and assigns the sequential numbers.

In the E2 formula, replace D2:D with the above bold part (twice) and that is the Part 2 formula.

This way we can code a running total by category formula that works in a relatively larger range in Google Sheets.

Before winding up, one more thing! We can use a Lambda Helper Function (LHF) for the same.

I’ve used that LHF to create a custom-named function called CUSUM_BY_GROUP.

Please check the fifth tutorial under “Resources” below.

That’s all. Thanks for the stay. Enjoy!

Resources

1. How to Calculate Running Balance in Google Sheets.
2. Reverse Running Total in Google Sheets (Array Formula).
3. How to Calculate a Horizontal Running Total in Google Sheets.
4. Reset Running Total at Every Year Change in Google Sheets (SUMIF Based).
5. Custom Named Function for Running Total by Group (Item, Month, or Year) In Google Sheets.