The AT_EACH_CHANGE Named Functions will help you insert the calculated output of summary functions at each group start row or end row in Google Sheets.

The summary functions are Sum, Count, Counta, Max, Min, Average, Product, StdDev (sample), StDevp (population), Var (sample), and Varp (population).

The function names will be SUM_AT_EACH_CHANGE, AVG_AT_EACH_CHANGE, MIN_AT_EACH_CHANGE, etc. and the syntax of all the functions will be the same.

For example, my =SUM_AT_EACH_CHANGE(A2:A13,B2:B13,1)) will place the subtotals in the first row of change in value.

On the contrary, the =SUM_AT_EACH_CHANGE(A2:A13,B2:B13,-1) will place it in the last row.

AT_EACH_CHANGE Named Functions – Syntax and Arguments

Actually, the Named Functions are a blessing for novice Google Sheets users.

Because they don’t worry about modifying complex formulas found online and end up in parse errors.

Even the experts can save lots of time by using them.

We have started a new section for Named Functions on this site, where you can find a growing list of custom functions that you can directly import to your Sheet and use.

Let’s go to the syntaxes of the AT_EACH_CHANGE Named Functions.

Since we have a list of functions (scroll down to see it) with the same arguments here, let’s take one of them and learn it.

Syntax: SUM_AT_EACH_CHANGE(group_range, subtotal_range, mode)

group_range: The range to test for value change. The functions require a sorted column.

subtotal_range: The range to Sum, Count, Counta, Average, Max, Min, Product, StdDev, StdDevp, Var, or Varp.

mode: In which row to insert the totals, in the first row (1) or end row (-1) of each group.

Usage Notes

• If your group range contains 100 rows, the formula requires an equal number of blank rows from the row it resides. Otherwise, it would return a #REF error.
• The group_range must not contain blank cells, which may cause #N/A errors in corresponding cells in the result column.
• You can remove those errors by wrapping the formula with the IFNA function.
• The AT_EACH_CHANGE Named Functions support open ranges such as A2:A and B2:B.

List of All AT_EACH_CHANGE Named Functions and Examples

Here is the list of the AT_EACH_CHANGE custom Named Functions you can download from the sample sheet at the end. It all follows the above same syntax.

• SUM_AT_EACH_CHANGE – Sum
• COUNT_AT_EACH_CHANGE – Count
• COUNTA_AT_EACH_CHANGE – Counta
• AVG_AT_EACH_CHANGE – Average
• MAX_AT_EACH_CHANGE – Max
• MIN_AT_EACH_CHANGE – Min
• PRODUCT_AT_EACH_CHANGE – Product
• STDEVS_AT_EACH_CHANGE – StdDev (StdDev.S)
• STDEVP_AT_EACH_CHANGE – StdDevp (StdDev.P)
• VARS_AT_EACH_CHANGE – Var (Var.S)
• VARP_AT_EACH_CHANGE – Varp (Var.P)

Please scroll up and see images # 1 and 2 for examples.

Here are some of the formulas which are present in those examples.

Max at Each Group change in D2 (image # 2):

=MAX_AT_EACH_CHANGE(A2:A13,B2:B13,-1)

Min at Each Group change in E2 (image # 2):

=MIN_AT_EACH_CHANGE(A2:A13,B2:B13,-1)

Average at Each Group change in G2 (image # 2):

=AVG_AT_EACH_CHANGE(A2:A13,B2:B13,-1)

How Do I Import and Use It?

First of all, make a copy of my example Sheet.

Example Sheet 231022

Then open your Sheet and find Named Functions under the Data menu. Select it.

On the sidebar panel that opens, click Import and import the functions you want from my copied Sheet.

That’s all.

I request you share your experience using my AT_EACH_CHANGE Named Functions that may help me develop more such functions for Google Sheets users in the future.

Thanks for the stay. Enjoy!