How to Use GROUPBY in Excel to Summarize Data

Excel’s GROUPBY function turns a list of repeated labels and numbers into a summary. For example, =GROUPBY(A1:A7,B1:B7,SUM,3,1) adds the amounts for each item, displays the source headers, and includes a grand total.

Use it when you want a formula-driven report that recalculates with its source cells. This guide uses a small accessory-order list so you can check every total before adapting the formula to your workbook.

Applies to: Excel for Microsoft 365. The example below was checked in Excel for Windows. If your installation does not recognize GROUPBY, see the alternatives at the end.

Build your first GROUPBY summary

1. Enter the sample below in A1:B7. Put the headers in row 1 and enter the amounts as numbers.
2. Select D1. Leave the cells below it and in column E empty so the result has room to expand.
3. Enter =GROUPBY(A1:A7,B1:B7,SUM,3,1) and press Enter.
4. Check the result. Cable should total 30, Hub 60, and Stand 60. The grand total should be 150.

The first range supplies the labels to group. The second supplies the amounts. SUM specifies the calculation; type the function name without parentheses. The 3 tells GROUPBY that both ranges include headers and that those headers should appear. The final 1 requests a grand total.

To check that the report is connected to its source, change B2 from 18 to 20. With automatic calculation enabled, Cable becomes 32 and the total becomes 152. Restore 18 when you finish testing.

Control headers, totals, and sort order

The full argument order is:

=GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array],[field_relationship])

You can usually leave the last arguments out. Set the header argument explicitly while learning; that avoids confusing a label row with a data row.

In the last formula, -2 means descending order by the second output column: Amount. Hub and Stand tie at 60, so their order does not change the meaning of the report.

For header handling, 0 means the input has no headers, 1 means it has headers that should be hidden, 2 generates headers for input without them, and 3 shows supplied headers. For totals, 0 hides them, 1 adds a grand total, and -1 puts that total first. Microsoft lists the remaining options in its GROUPBY reference.

Average, count, or filter the orders

Replace SUM with AVERAGE to calculate the average order amount per item:

=GROUPBY(A2:A7,B2:B7,AVERAGE,0,0)

The sample averages are Cable 15, Hub 30, and Stand 30. To count numeric amounts instead, use COUNT; each item has two. Use COUNTA when the values you are counting are nonempty text, rather than numbers.

To include only orders worth at least 20, use the seventh argument:

=GROUPBY(A2:A7,B2:B7,SUM,0,0,-2,B2:B7>=20)

This returns Stand 60 and Hub 45. Cable disappears because neither of its two orders qualifies. Notice that the condition tests individual source rows; it does not test each item’s final total.

Keep the filter condition aligned with both input ranges. Here, all three cover rows 2 through 7. Including the header in only one of those ranges is a common cause of trouble.

Group by two fields or let the input grow

If a different worksheet has Region in A, Item in B, and Amount in C, you can group by each region/item combination:

=GROUPBY(A1:B20,C1:C20,SUM,3,2)

The two-column grouping range allows subtotals, requested by 2, as well as a grand total. This example assumes row 1 contains headers and rows 2–20 contain records. Change those bounds to match your data.

A fixed reference such as A1:A7 does not automatically include a new order typed in row 8. For a regularly growing list, convert the input to an Excel Table and use its actual table and column names. If the table is named Orders, a data-only formula would be:

=GROUPBY(Orders[Item],Orders[Amount],SUM,0,1)

Place the GROUPBY formula outside the table. The input can be a table, but the expanding result needs ordinary worksheet cells. Microsoft’s structured-reference guide explains how table references adjust as data changes.

Fix an error or choose a different reporting tool

• #SPILL!: Inspect the intended output area for existing values or merged cells. Move the formula to clear space outside any Excel Table.
• #NAME? or _xlfn.GROUPBY: Confirm the spelling and whether your Microsoft 365 installation includes the function. An older perpetual version of Excel does not gain it merely by opening this workbook.
• #VALUE! after adding subtotals: Use two or more grouping fields for subtotals. For the one-field accessory example, use total depth 0 or 1.
• Unexpected separate groups: Inspect the source labels for inconsistent spelling or extra spaces. “Cable” and a label with an extra space may not represent the same group.
• Wrong or incomplete total: Check that every intended row is in the ranges and that amounts are numeric. Compare the summary with the source before using it in a report.

GROUPBY is convenient when another formula needs to use the summary. A PivotTable is often a better choice when you want to rearrange fields interactively or use its reporting tools. It is also a practical alternative when GROUPBY is unavailable. For a single total that meets several conditions, SUMIFS may be all you need.

Matt Jacobs

Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.

His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.