Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 68 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 hack, you can count each unique
entry only once.

Consider the list in Figure 6-10, which has been sorted so that you can see
multiple entries easily.

Figure 6-10. 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 [Hack #65])

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.