Count unique entries in a range of data

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 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

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 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

1. Select the column containing the data.

   

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 data 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.

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 range.
8. Click the arrow next to AutoSum on the Standard toolbar, and then click Count.
9. Press Enter.