Custom Named Function for Running Total by Group (Item, Month, or Year) In Google Sheets

Published on

What about an all-in-one custom Named Function that can return the running total by group (item, month, or year) in Google Sheets?

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

My CUSUM_BY_GROUP() custom Named Function can return the running total by the specified group type.

It may be one of the simplest solutions for such calculations that you may have come across so far.

One of the salient features of this function is that it doesn’t require your group column to be sorted in any order.

You can either sort your data or not. That doesn’t affect the result.

I’ve already shared independent formulas for item/month/year-wise running totals. But not a custom all-in-one Named Function as mentioned above.

Here are those previous tutorials for your quick reference.

CUSUM_BY_GROUP Custom Named Function for Running Total by Group

To use, you must first import the said CUSUM_BY_GROUP function. Here are the steps that you should follow.

How to Import

Steps:

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

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

CUSUM_BY_GROUP copy

Function Syntax and Arguments

Syntax:

CUSUM_BY_GROUP(group_range, cusum_range, type)

Arguments:

group_range: The array or range to check for group.

cusum_range: The partial sum (running total) column.

Both arguments take one column each, and the number of rows must be the same. Otherwise, the formula will return the “Array arguments to MAP are of different size” error.

type: The types are month, year, or blank.

If the group_range contains dates, you can specify “month” or “year” for the custom-named function to return the group-wise running total based on month or year.

If you want the CUSUM_BY_GROUPY custom Named function to return the regular (item/category-wise) group-wise running total, leave the last argument.

Formula Examples – Running Total by Group Using a Custom Named Function

Example 1 (Item Wise):

In the first example, I have items in C2:C11 and their amount in D2:D11.

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

The following formula in E2 based on the custom Named function CUSUM_BY_GROUP returns the running total by item in E2:E11.

In the formula, C2:C11 is the group_range , and D2:D11 is the cusum_range.

I’ve omitted the type as it’s only required when we want to return the running total by month or year grouping.

Custom Named Function - Running Total by Item

When opening the range, I mean using entire columns in the formula, use it as follows.

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

The same IF logical test applies to the other two formulas below.

Example 2 (Month Wise):

This time we have a date column to use as group_range. So if you use the example # 1 formula without the type argument, you will get the running total by the dates.

Just specify “month” in the last argument to return the running total based on month grouping.

=CUSUM_BY_GROUP(C2:C11,D2:D11,"month")
Custom Named Function - Running Total by Month

Example 3 (Year Wise):

=CUSUM_BY_GROUP(C2:C11,D2:D11,"year")
Custom Named Function - Running Total by Year

The above formula, that based on my custom Named Function, will return the running sum by year.

That’s all about the CUSUM_BY_GROUP (cumulative sum by group) function and how to use it in your sheet.

Thanks for the stay. Enjoy!

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.

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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.