Microsoft Office Tutorials and References

In Depth Information

**Tip 44: Counting Nonduplicated Entries in a Range**

Counting Nonduplicated Entries in a

Range

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

44-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 6, which is the number of nonduplicated

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

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

To adapt this formula to your own worksheet, just change both instances of A1:A10 to the range

address that you’re working with.

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

Note

Figure 44-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 websites and

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