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:

DCOUNTA(database,field,criteria),

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

(or any heading that is not the same as the field or column heading). Below this, in cell D2, enter this formula:**Criteria**

=COUNTIF($A$2:A2,A2)=1

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:

=DCOUNTA($A$1:$A$100,1,$D$1:$D$2)

This will use the criteria to exclude duplicates and give you the result you need, which is 11, as there are 11 unique names.

updated