AT_EACH_CHANGE Named Functions in Google Sheets

Published on

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.

AT_EACH_CHANGE Named Functions - At Top Row of Each Group
image # 1

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 - At Bottom Row of Each Group
image # 2

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!

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 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...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

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...

6 COMMENTS

  1. Hi, cool custom function! Works well. I might suggest it would be more effective as a single function and a CHOOSE function that selects which of the 11 functions to use (same as the existing SUBTOTAL function).

    Also, you might already know this, but for Google Sheet’s custom functions, you don’t have to use the outer LAMBDA wrapper as the custom function interface already does that. Just start with the BYCOL part and it is easier to enter and adapt later. 🙂

    Thanks a ton for all your posts and tips.

  2. Hi Prashanth,

    Thanks for the help.

    I solved the problem using the Query function with Group and Pivot.

    I put the notes of the periods in separate columns, and I used a formula from infoinspired.com to calculate the average, as you mentioned in a helper column.

  3. Hi Prashanth,

    Thank you very much for AT_EACH_CHANGE Named Functions.

    I work in a school, and I need to calculate the average of the students in several subjects over the three periods of the year.

    It is like using average if set, where the criteria are the name of the student, the subject, the period, and the respective grade.

    • Hi, Harvi,

      It might be possible. You must use a helper column to combine other criteria columns like =Arrayformula(A2:A&B2:B&C2:C) and use that in group_range.

      I could help if you include an example sheet URL in your reply below.

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.