Count unique entries in a list

The method for this task depends on the result you want to see.

Count the occurrence of a value in a range of cells

Use the COUNTIF function to do this task.

Example

The example may be easier to understand if you copy to a blank worksheet.

How?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
3
4
5
6
7
A B
Salesperson Invoice
Buchanan 15,000
Buchanan 9,000
Suyama 8,000
Suyama 20,000
Buchanan 5,000
Dodsworth 22,500
Formula Description (Result)
=COUNTIF(A2:A7,"Buchanan") Number of invoices for Buchanan (3)
=COUNTIF(A2:A7,A4) Number of invoices for Suyama (2)

Function details

COUNTIF

Count the occurrence of more than one condition

Use the IF and SUM functions to do this task.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
3
4
5
6
7
A B
Salesperson Invoice
Buchanan 15,000
Buchanan 9,000
Suyama 8,000
Suyama 20,000
Buchanan 5,000
Dodsworth 22,500
Formula Description (Result)
=SUM(IF((A2:A7="Buchanan")+(A2:A7="Dodsworth"),1,0)) Number of invoices for Buchanan or Dodsworth (4)
=SUM(IF((B2:B7<9000)*(B2:B7>10),1,0)) Number of invoices with values less than 9000 or greater than 19000 (3)
=SUM(IF(A2:A7="Buchanan",IF(B2:B7<9000,1,0))) Number of invoices for Buchanan with a value less than 9,000. (1)

Note   The formulas in this example must be entered as array formulas. Select each cell that contains a formula, press F2, and then press CTRL+SHIFT+ENTER.

Function details

SUM

IF

Count the occurrences of unique entries

  1. Select the column containing the list.

  2. On the Data menu, click PivotTable and PivotChart Report.
  3. Click Finish.
  4. Drag the column label from PivotTable Field List to Drop Row Fields Here.
  5. Drag the same column label from PivotTable Field List to Drop Data Items Here.

Note   If your list contains numbers, the PivotTable report totals the entries instead of counting them. To change from the Sum summary function to the Count summary function, on the PivotTable toolbar, click Field Settings , and then in the Summarize by box, click Count.

Count the number of unique values

For example, if a column contains 1,2,2,2 the result is 2 unique values in the column.

  1. On the Data menu, point to Filter, and then click Advanced Filter.
  2. In the Advanced Filter dialog box, click Copy to another location.
  3. In the List range box, delete any information in the box or click the box, and then click the column for which you want to count the unique entries.
  4. If you have a column label, in the Criteria range box, delete any information in the box or click the box, and then click the column label for your column of data.
  5. In the Copy to box, delete any information in the box or click the box, and then click a blank column where you want to copy the unique values.
  6. Select the Unique records only check box, and click OK.
  7. Select the blank cell below the last cell in the list.
  8. Click the arrow next to AutoSum on the Standard toolbar, and then click Count.
  9. Press Enter.