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.
Search JabSto ::




Custom Search