Microsoft Excel —

Count Only One Instance of Each Entry in a List

When you have a large list of items, you might want to perform a count on the items without counting entries that appear multiple times. With this tutorial, you can count each unique entry only once.

Consider the list in the figure, which has been sorted so that you can see multiple entries easily.

Figure. Range of sorted names
figs/exhk_0610.gif

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 Criteria (or any heading that is not the same as the field or column heading). Below this, in cell D2, enter this formula:

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