The GROUPBY function was introduced in March 2024 in Excel 365. It allows users to effortlessly create dynamic COUNTIF or SUMIF functions. This article explains its use and provides simple examples.

What Does the GROUPBY Function Do?

The function helps group data and calculate summaries, similar to a pivot table.

Advantages over pivot tables:

• It updates instantly when the source data changes.
• Many pieces of information are returned (labels, values, calculation) with a single formula.

Let’s explore how to build and use the GROUPBY function.

How to Write the GROUPBY Function

Here’s what you need to include in the GROUPBY formula:

1. Row labels: Specify the column for grouping, such as "Region."
2. Values to group: Indicate the column to calculate, like "Sales."
3. Calculation type: Choose from sum, count, max, or min.
4. Show headers: Decide if headers should appear in the result.
5. Grand total: Add a grand total if needed.
6. Sort order: indicate the columns index to sort (1, 2, 3, ...) and increasing ( + ) or decreasing ( - )

Don't be afraid by the number of arguments. A tooltip helps you to fill each argument correctly 😉

Examples to Use the Function

#1: Count Sales by Region

In this example, we analyze sales by region using the GROUPBY function. Follow these steps to write the formula:

1. Select the column for the Region labels.
2. Select the column with the Sales data.
3. Choose the calculation type: COUNT.

This single formula gives the number of sales records for each region. Let’s compare it with the COUNTIF function:

• First, list each region manually or use the UNIQUE function.
• Then, create a COUNTIF formula for each region.

The GROUPBY function simplifies this process, saving time and reducing errors 😃👍

#2: SUM of Sales per Region

By simply changing the last argument to SUM, you can easily analyze total sales, replacing the need for SUMIF.

Grouping by Multiple Columns

You can also build a GROUPBY function with the CHOOSECOLS function to create a virtual array. For instance, in this example, we have used as first argument, an array with the column 1 and 3 of our original source.

Why Use the GROUPBY Function?

The GROUPBY function simplifies data analysis in Excel, offering more flexibility than pivot tables.

• It’s dynamic, fast, and works directly in formulas.
• Group multiple columns, even when they’re not contiguous.
• In one single formula, you can build a full dashboard analysis

Start using the GROUPBY function today and enhance your Excel skills!