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

By Ali, Founder, Excel Lessons

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.

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

Column D lists each customer once, as UNIQUE would spill it.

In E2, type:

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

Lock the log so it fills down.

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, as UNIQUE would spill it. In E2, type: =COUNTIF($B$2:$B$8,D2). Lock the log so it fills down.

    Hint. Locked log, D2 as the criterion.

  2. 2

    In F2, total Acme's hours with SUMIF over the log.

    Hint. SUMIF needs three parts: log, name, hours.

  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, find the busiest customer with INDEX and SORT. Shape: =INDEX(SORT(table, col, n), 1, 1). Sort by ticket count, descending.

    Hint. SORT by column 2, -1.

  5. 5

    In F8, find Acme's highest hours with MAX and FILTER.

    Hint. MAX of the FILTER.