Consider the list in the figure, which has been sorted so that you can see multiple entries easily.
Figure. Range of sorted names
A normal count on this list (using
COUNTA) would result in the names Bill W, Dave H, Fran T, Frank W, and Mary O being counted more that once. The
DCOUNTA function offers an alternative that is very efficient and easy to modify.
The syntax of the
DCOUNTA function is as follows:
The arguments for this function are the same as those for the
DSUM function described in Convert Dates
Building on the preceding list, in cell D1 enter the word
Criteria (or any heading that is not the same as the field or column heading). Below this, in cell D2, enter this formula:
Note the combination of relative (A2) references and absolute ($A$2) references! These are vital to the criteria working.
Now, in the cell where you want your result shown, enter this function:
This will use the criteria to exclude duplicates and give you the result you need, which is 11, as there are 11 unique names.