Running Total by Category in Google Sheets (SUMIF Based)

Published on

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.

Example to Running Total by Category in Google Sheets
screenshot # 1

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.

Running Total (Cumulative Sum) by Category Explained
screenshot # 2

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.

Part 2 SUMIF Logic
screenshot # 3

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.

P1001
A1512
Q1013

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.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Highlight Upcoming Birthdays in Google Sheets

When highlighting upcoming birthdays in Google Sheets, one important aspect is ignoring the year...

Compare Two Tables for Differences in Excel

To compare two tables with similar data for differences, you can use the XLOOKUP...

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

More like this

Highlight Upcoming Birthdays in Google Sheets

When highlighting upcoming birthdays in Google Sheets, one important aspect is ignoring the year...

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.