Lessons Lesson 98

GROUPBY: The Summary in One Formula

About this lesson

GROUPBY takes a column to group by, a column to aggregate, and the aggregate to use. It returns one row per group, with a grand total under them. The whole summary spills from the cell you typed in.

By the end you can

  • summarise a column by group with one GROUPBY formula.
  • change the aggregate by changing one word.
  • read a single figure back out of the summary.

Practises GROUPBY SUMIF INDEX

By Ali, Founder, Excel Lessons

The idea

The last three lessons built a summary by hand: list the groups, then a SUMIF against each one. GROUPBY does all of it from one formula. You do not list the groups. It finds them, in the order they first appear. It works out how many there are.

The third argument is the odd one. It is the aggregate, written as a bare name: SUM, not "SUM" in quotation marks. That is a function being passed to a function. It is new in Excel and it reads strangely the first time. Swap SUM for COUNT or AVERAGE and the same formula answers a different question.

The mistake to watch for

Typing under a GROUPBY. The summary spills, and you did not decide how tall it is. A group added to the data makes it one row taller. Anything sitting in the way turns the whole answer into #SPILL!. Leave the block below it clear, and leave a spare row for the group that has not happened yet.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

GROUPBY takes what to group by, what to add up, and how.

In E2, type:

=GROUPBY(A2:A6,C2:C6,SUM)

E2
Row A B C D E F G
1 Region Product Amount By region South total
2 North Bikes 500
3 South Bikes 200
4 North Cars 300
5 South Cars 100
6 North Bikes 150 By product
7
8

Every step

  1. 1

    GROUPBY takes what to group by, what to add up, and how. In E2, type: =GROUPBY(A2:A6,C2:C6,SUM).

    Hint. Three arguments, and the last one is a bare SUM.

  2. 2

    The summary is an array, so INDEX reads it by row and column. Read =INDEX(GROUPBY(A2:A6,C2:C6,SUM),1,2) and say what it returns.

    Hint. The first group's total.

  3. 3

    The last row of a GROUPBY is the grand total. Read =INDEX(GROUPBY(A2:A6,C2:C6,SUM),3,2) and say what it returns.

    Hint. Add up all five amounts.

  4. 4

    Change the first argument and the same formula answers a different question. In E7, use GROUPBY to summarise by product.

    Hint. Column B instead of column A.

  5. 5

    In G2, take South's total out of the region summary with INDEX around GROUPBY.

    Hint. South is the second group.