Microsoft Office Tutorials and References

In Depth Information

**Counting Nonduplicated Entries in a Range**

Counting Nonduplicated Entries in a Range

In some situations, you might need to count the number of nonduplicated entries in a range.

Figure 117-1 shows an example. Column A has a list of animals, and the goal is to count the

number of different animals in the list. The formula in cell B2 returns 8, which is the number of

nonduplicated animals. This formula (an array formula, by the way) is

=SUM(1/COUNTIF(A1:A10,A1:A10))

When you enter an array formula, press Ctrl+Shift+Enter (not just Enter). Excel

surrounds the formula with braces to remind you that it’s an array formula.

Figure 117-1:
Use an array formula to count the number of nonduplicated entries in a range.

This formula is one of those “Internet classics” that is passed around on various Web sites and

newsgroups. Credit goes to David Hager, who first came up with the formula.

The preceding array formula works fine unless the range contains one or more empty cells. The

following modified version of this array formula uses the new IFERROR function to overcome this

problem:

=SUM(IFERROR(1/COUNTIF(A1:A10,A1:A10),0))

The preceding formulas work with both values and text. If the range contains only numeric values

or blank cells (but no text), you can use the following formula (which isn’t an array formula) to

count the number of nonduplicated values:

=SUM(N(FREQUENCY(A1:A10,A1:A10)>0))