Microsoft Excel Functions & Formulas

Use the SUM and COUNTIF functions to count

unique records in a list

Excel offers a feature to extract unique values from a list. This

feature usually is used by filtering the list through the Data menu

option Filter | Advanced Filter. But how do you count unique

records in a list without filtering them? Use the SUM and

COUNTIF functions together in an array formula.

4

To count unique records in a list:

1. In cells A2:A11 list numbers, repeating some.

2. Select cell C2 and type the following array formula:

=SUM(1/COUNTIF($A$2:$A11,$A$2:$A11)).

3. Press <Ctrl+Shift+Enter>.

Working with Array Formulas 297

11

Figure 11-19