The AT_EACH_CHANGE Named Functions offer a versatile way to insert calculated summary outputs, such as totals or other aggregations, at each group change in Google Sheets. These functions simplify repetitive tasks, making them invaluable for users managing grouped data.

Let’s explore their capabilities and usage in detail.

What Are AT_EACH_CHANGE Named Functions?

The AT_EACH_CHANGE Named Functions help you insert the calculated output of summary functions at either the start or the end of each group in a dataset. These summary functions include:

• Sum
• Count
• Counta
• Max
• Min
• Average
• Product
• Standard Deviation (sample/population)
• Variance (sample/population)

Each function has a unique name, such as SUM_AT_EACH_CHANGE, AVG_AT_EACH_CHANGE, MIN_AT_EACH_CHANGE, etc., but shares the same syntax for consistency.

For instance:

• =SUM_AT_EACH_CHANGE(A2:A13, B2:B13, 1) inserts the subtotal in the first row of each group.

• =SUM_AT_EACH_CHANGE(A2:A13, B2:B13, -1) inserts the subtotal in the last row of each group.

Syntax and Arguments

Here’s the syntax for these Named Functions:

FUNCTION_NAME(group_range, subtotal_range, mode)

Arguments:

1. group_range: The range to test for value changes. Ensure this range is sorted.
2. subtotal_range: The range to calculate the aggregation (Sum, Count, etc.).
3. mode: Determines the placement of the summary values. Use 1 for the first row and -1 for the last row of each group.

Usage Notes

• Ensure that the group_range does not contain blank cells, as they may cause #N/A errors. If blank cells are unavoidable, wrap the formula with IFNA to handle these errors gracefully. Note that blank rows are excluded from the aggregation.
• If your group_range contains 100 rows, ensure there are 99 blank rows below the formula to prevent #REF errors.
• The functions support open ranges (e.g., A2:A), allowing them to dynamically adjust to changes in your dataset. When using open ranges, consider wrapping the formula with IFNA for error handling.

Advantages of AT_EACH_CHANGE Named Functions

1. User-Friendly: Ideal for beginners, eliminating the complexity of modifying advanced formulas.
2. Time-Saving: Experts can quickly implement complex operations without repeated manual adjustments.
3. Reusable: Easily import these functions into your Sheets and apply them to any dataset.

We’ve curated a growing library of custom Named Functions that you can explore and use. Visit our dedicated section here to find more.

Examples

Below are examples of common aggregations using the AT_EACH_CHANGE Named Functions. For visual reference, see Images #1 and #2.

1. Max at Each Group Change:

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

2. Min at Each Group Change:

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

3. Average at Each Group Change:

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

How to Import and Use These Functions

Follow these steps to start using the AT_EACH_CHANGE Named Functions:

1. Make a Copy: Copy the example sheet linked below.
   • Sample Sheet
2. Open Named Functions: In your Google Sheet, go to the Data menu and select Named Functions.
3. Import Functions: Click Import in the sidebar and select the desired functions from the copied example sheet.

That’s it! You’re ready to use these powerful functions.

Complete List of Functions

Below is the complete list of AT_EACH_CHANGE Named Functions you can import and use. All functions follow the 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 (sample)
• STDEVP_AT_EACH_CHANGE – StdDevp (population)
• VARS_AT_EACH_CHANGE – Var (sample)
• VARP_AT_EACH_CHANGE – Varp (population)

Final Thoughts

The AT_EACH_CHANGE Named Functions are a game-changer for anyone working with grouped data in Google Sheets. They’re simple, flexible, and save a significant amount of time.

Have you tried these functions? Share your feedback and let us know how they’ve helped you. Your input can inspire the development of even more powerful tools for Google Sheets users.

Thanks for visiting, and happy data processing!