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.
- Reset Running Total at Every Year Change in Google Sheets (SUMIF Based).
- Running Total with Monthly Reset in Google Sheets (Array Formula).
- Array Formula for Conditional Running Total in Google Sheets.
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:
- First, get a copy of my sample sheet from the download option (copy button) below.
- Open your Sheet in which you want to use the CUSUM_BY_GROUP() function.
- Go to Data > Named function > Import function.
- 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.
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.
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")
Example 3 (Year Wise):
=CUSUM_BY_GROUP(C2:C11,D2:D11,"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!
 in Google Sheets?){kind=link}