Important Notice: Our web hosting provider recently started charging us for additional visits, which was unexpected. In response, we're seeking donations. Depending on the situation, we may explore different monetization options for our Community and Expert Contributors. It's crucial to provide more returns for their expertise and offer more Expert Validated Answers or AI Validated Answers. Learn more about our hosting issue here.

Can I write a formula that returns the number of distinct entries in a range?

April 26, 2017distinct entries Formula range returns write

0

Posted

Can I write a formula that returns the number of distinct entries in a range?

1 Answer

0

Posted

First, let’s clarify the question. We’re hunting for a formula that, given the range 100, 99, 98, 100, 98, 100, 98, would return 3. 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.