Counting Unique Values with Functions

[OpenCast Excel Tips]

David has a worksheet in which there is a list of countries. This
list, in cells A1:A100, can contain duplicates. David wants to
determine the number of unique countries in the list.

There are several ways you can go about deriving a count, without
resorting to using a macro. The method you should use depends on the
characteristics of the data in the list. A good place to start,
however, is to define a named range that represents the list of
countries. In the following examples, it is assumed that the range is
named Countries. (Catchy name, huh?)

If the list contains only text entries, and does not contain any blank
cells, then the following will provide a count:

=SUM(1/COUNTIF(Countries,Countries))

This should be entered as an array formula, by pressing Ctrl+Shift
+Enter. If the list contains blank cells, then the formula becomes a
bit more complex. The following long array formula will work if there
are blanks:

=SUM(IF(FREQUENCY(IF(LEN(Countries)>0,MATCH
(Countries,Countries,0),""), IF(LEN(Countries)
>0,MATCH(Countries,Countries,0),""))>0,1))

This approach—using the FREQUENCY function—is fully recounted in the
Microsoft Knowledge Base:

http://support.microsoft.com/?kbid=100122

If you prefer to not use array formulas (for whatever reason), you can
utilize a blank column to the right of your list. This column will
contain regular formulas that indicate if the value to its left is
unique in the list or not. The first time a value appears, the formula
returns the number 1. On each subsequent appearance of the same value,
the formula returns a 0. Start by sorting your list, then place the
following formula in cell B1:

=IF(ISNA(VLOOKUP(A2,$A$1:A1,1,)),1,0)

Just copy the formula from B1 to the range B2:B100. With these results
in place, you can easily sum column B and have a count of the unique
values in the list.