Excel Practice
Lessons Lesson 89

A Frequency Table: UNIQUE with COUNTIF

About this lesson

A frequency table in Excel is a UNIQUE list of the labels, with a COUNTIF beside each one. SUMIF or AVERAGEIF in further columns give the other summaries.

By the end you can

  • build a frequency table with UNIQUE and COUNTIF.
  • find the busiest customer with SORT and INDEX.

Practises UNIQUE COUNTIF SUMIF SORT FILTER

The idea

The most common report there is: one row per customer, product or day, with a count and a total. UNIQUE produces the labels once. COUNTIF and SUMIF against the locked log fill in the numbers. SORT and INDEX find the biggest. In Excel 365 the label column is one spilled formula, and the rest fill down beside it.

The mistake is the lock. The log range in every COUNTIF and SUMIF must be absolute. Otherwise the second row counts a log one row shorter than the first. Do the counts down a frequency table add up to the number of rows in the log? If not, that is where to look.

The mistake to watch for

The lock. The log range in every COUNTIF and SUMIF must be absolute. Otherwise the second row of the table counts a log one row shorter than the first. Do the counts down a frequency table add up to the number of rows in the log? If not, that is where to look.

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

Column D lists each customer once. In Excel 365 it would be one UNIQUE spilled down. In E2, count Acme's tickets with COUNTIF against the log. Lock the log so it fills down.

To begin, type it exactly:

=COUNTIF($B$2:$B$8,D2)

E2
Row A B C D E F G
1 Ticket Customer Hours Customer Tickets Hours
2 101 Acme 2 Acme
3 102 Birch 5 Birch
4 103 Acme 1 Cedar
5 104 Cedar 3 Elm
6 105 Birch 2
7 106 Acme 4 Busiest
8 107 Elm 1

Every step

  1. 1

    Column D lists each customer once. In Excel 365 it would be one UNIQUE spilled down. In E2, count Acme's tickets with COUNTIF against the log. Lock the log so it fills down. To begin, type it exactly: =COUNTIF($B$2:$B$8,D2).

    Hint. Locked log, D2 as the criterion.

  2. 2

    In F2, Acme's hours: SUMIF over the same log, adding the hours column.

    Hint. SUMIF, three arguments.

  3. 3

    Filled down, E5 reads =COUNTIF($B$2:$B$8,D5). What does it show for Elm?

    Hint. One Elm ticket.

  4. 4

    In E8, the busiest customer. Sort the summary D2:E5 by its ticket count, descending, and read the name in row 1 with INDEX.

    Hint. SORT by column 2, -1.

  5. 5

    In F8, write a formula using MAX and FILTER that gives the most hours on any single Acme ticket.

    Hint. MAX of the FILTER.