Excel Practice
Lessons Lesson 91

SUBTOTAL: One Function, Nine Aggregates

About this lesson

SUBTOTAL applies one of nine 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

The idea

One function that stands in for nine, chosen by a number. 9 is SUM, 1 is AVERAGE, 2 is COUNT, 3 is COUNTA, 4 is MAX, 5 is MIN. 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.

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

In C5, total the North sales with SUBTOTAL. Its first argument is a function number, and 9 means SUM.

To begin, type it exactly:

=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

    In C5, total the North sales with SUBTOTAL. Its first argument is a function number, and 9 means SUM. To begin, type it exactly: =SUBTOTAL(9,C2:C4).

    Hint. 9 first, then C2:C4.

  2. 2

    In C9, the South total the same way.

    Hint. Second section.

  3. 3

    A plain SUM over the whole column would count the section totals as well as the sales. Read =SUM(C2:C9) and say what it returns.

    Hint. The subtotals are in the range too.

  4. 4

    In C10, the grand total. SUBTOTAL with 9 over the two sections, given as two separate ranges.

    Hint. Two ranges.

  5. 5

    In C11, count the reps with SUBTOTAL and function number 3, which is COUNTA.

    Hint. 3 for COUNTA.

  6. 6

    In C12, write a formula using SUBTOTAL that gives the largest single sale on the sheet.

    Hint. 4 for MAX.