Counting Distinct Entries In A Range
Category: Formulas | [Item URL]
Q. Can I write a formula that returns the number of distinct entries in a range?First, let's clarify the question. We're hunting for a formula that, given the range that contains the values 100, 99, 98, 100, 98, 100, 98, would return 3. In other words, this range contains three different values, some of them repeated.
This type of counting requires an array formula. The formula below, for example, counts the number of distinct entries in the range A1:D100.
=SUM(1/COUNTIF(A1:D100, A1:D100))
When you enter this formula, you must press Ctrl-Shift-Enter. Pressing only Enter will give you the wrong result. Excel will place brackets around the formula to remind you that you've created an array formula.
The preceding formula works fine in many cases, but it will return an error if the range contains any blank cells. The formula below (also an array formula, so input it with Ctrl-Shift-Enter) is more complex, but it will handle a range that contains a blank cell.
=SUM(IF(COUNTIF(A1:D100,A1:D100)=0, "", 1/COUNTIF(A1:D100,A1:D100)))
Excel Tips
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
All Tips
Browse Tips by Category
Search for Tips
Tip Books
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel | Other Excel 2003 books | Amazon link: John Walkenbach's Favorite Excel Tips & Tricks
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks