Lessons Lesson 91

SUBTOTAL: One Function, 11 Aggregates

About this lesson

SUBTOTAL applies one of 11 aggregates to the ranges that follow. Its first argument chooses which. 9 for SUM, 1 for AVERAGE, 3 for COUNTA, 4 for MAX. It is the function a sectioned report puts under each section.

By the end you can

  • put a SUBTOTAL under each section of a report.
  • pick the aggregate with the function number.
  • write a grand total that does not double count.

Practises SUBTOTAL SUM COUNTA MAX

By Ali, Founder, Excel Lessons

The idea

One function that stands in for 11, chosen by a number. 9 is SUM, 1 is AVERAGE, 2 is COUNT, 3 is COUNTA, 4 is MAX, 5 is MIN, 6 is PRODUCT. The last four are standard deviation and variance. This grid does not run those. Its home is a report with sections. A SUBTOTAL sits under each block of rows.

Two things it does in desktop Excel that this grid cannot show. It ignores rows hidden by a filter, so a filtered report totals only what is visible. And it ignores other SUBTOTAL cells inside its range, so a grand total over the whole column counts each sale once. Neither depends on anything a lesson can type. So the safe grand total here names its sections. That also works on every version of Excel, and with section totals written as plain SUMs.

The mistake to watch for

A plain SUM over a column that already holds subtotals. It cannot tell a section total from a sale, and it counts every figure twice. Name the sections in the grand total. Or use SUBTOTAL in desktop Excel, which skips other SUBTOTAL cells in its range and ignores rows a filter has hidden.

Read more about this

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

SUBTOTAL's first argument is a number that picks the aggregate. 9 means SUM.

In C5, type:

=SUBTOTAL(9,C2:C4)

C5
Row A B C D E F G
1 Region Rep Sales
2 North Ada 420
3 North Ben 310
4 North Cai 275
5 North total
6 South Dee 505
7 South Eli 180
8 South Fay 390
9 South total
10 Grand total
11 Reps
12 Best sale

Every step

  1. 1

    SUBTOTAL's first argument is a number that picks the aggregate. 9 means SUM. In C5, type: =SUBTOTAL(9,C2:C4).

    Hint. 9 first, then C2:C4.

  2. 2

    In C9, total the South sales with SUBTOTAL.

    Hint. Same idea, for the second section.

  3. 3

    A plain SUM would count the section totals too. Read =SUM(C2:C9) and say what it returns.

    Hint. The subtotals are in the range too.

  4. 4

    In C10, total both sections with SUBTOTAL. Shape: =SUBTOTAL(n, range, range).

    Hint. Two ranges.

  5. 5

    In C11, count the reps with SUBTOTAL, using 3 for COUNTA.

    Hint. 3 for COUNTA.

  6. 6

    In C12, find the largest sale with SUBTOTAL, function 4.

    Hint. 4 for MAX.