What if there was an all-in-one custom Named Function that could return the running total by group—whether by item, month, or year—in Google Sheets?

I mean a single function where you can easily specify whether you want the running total by item, month, or year.

My CUSUM_BY_GROUP() custom Named Function does exactly that: it returns the running total based on the specified group type.

It may be one of the simplest solutions for such calculations that you’ll come across.

One of the key advantages of this function is that it doesn’t require your group column to be sorted. You can sort your data—or not—and it will still return accurate results.

I’ve already shared independent formulas for item-, month-, and year-wise running totals. But until now, I hadn’t shared a custom all-in-one Named Function like this.

You can check those tutorials in the Resources section at the bottom of this post.

CUSUM_BY_GROUP() Named Function for Running Totals by Group

To use CUSUM_BY_GROUP(), you first need to import the function into the Google Sheets file where you want to use it. Follow these steps:

How to Import

1. Get a copy of my sample sheet from the download option (copy button) below.
2. Open the Google Sheet where you want to use the CUSUM_BY_GROUP() function.
3. Go to Data > Named function > Import function.
4. Search for the copied file name, likely “Copy of Cumulative Sum by Group – II”, and import.

Get CUSUM_BY_GROUP

You can read more about these steps in my tutorial How to Create Named Functions in Google Sheets.

Function Syntax and Arguments

Syntax:

CUSUM_BY_GROUP(group_range, cusum_range, type)

Arguments:

• group_range: The array or range to check for grouping.
• cusum_range: The column containing values to calculate the running total.
  • Both arguments must have the same number of rows. Otherwise, the formula will return the error: “Array arguments to MAP are of different size.”
• type: Optional. Can be "month", "year", or left blank (simply use a trailing comma if omitting it).
  • If group_range contains dates, specify "month" or "year" to return the running total grouped by month or year.
  • Leave it blank to return a regular item- or category-wise running total. Suitable when your group column contains item names or parts of dates such as year, month, or week.

Examples: Running Totals by Group with Named Function

Example 1 – Item-wise Running Total

Suppose items are in C2:C11 and their amounts are in D2:D11.

=CUSUM_BY_GROUP(C2:C11, D2:D11,)

This formula in E2 returns the running total by item in E2:E11.

• C2:C11 → group_range
• D2:D11 → cusum_range
• The type argument is omitted because we are grouping by items.

Using Entire Columns:

=ArrayFormula(IF(C2:C="",,CUSUM_BY_GROUP(C2:C,D2:D,)))

The same IF logical test can be applied to formulas for month- and year-wise running totals.

Example 2 – Month-wise Running Total

If your group_range contains dates, using the formula from Example 1 without the type argument will give a running total by date. To group by month, specify "month" as the third argument:

=CUSUM_BY_GROUP(C2:C11, D2:D11, "month")

Example 3 – Year-wise Running Total

Similarly, to get the running total grouped by year:

=CUSUM_BY_GROUP(C2:C11, D2:D11, "year")

This returns the running sum by year in your specified range.

That’s all about the CUSUM_BY_GROUP() custom Named Function and how to use it in Google Sheets.

Enjoy simpler and more flexible running totals in your spreadsheets!

Resources

• Normal and Array Running Totals in Google Sheets
• Array Formula for Conditional Running Total in Google Sheets
• How to Reset a Running Total by Year in Google Sheets
• Running Total by Category in Google Sheets
• How to Reset a Running Total by Month in Google Sheets
• Weekly and Biweekly Running Totals in Google Sheets
• Reset Running Total at Blank Rows in Google Sheets
• Running Total with Multiple Subcategories in Google Sheets
• Running Sum with Negative Value Carryover in Google Sheets