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))

Search JabSto ::

Custom Search