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

Here you will find a lot of Frequently Asked Questions.

To find the number of unique items in a range you should enter the following formula

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

or you could use the following that will not bring an error if there are some blanks

 =SUM(IF(COUNTIF(A1:D100,A1:D100)=0, "", 1/COUNTIF(A1:D100,A1:D100)))

or try this one too =SUM(IF(LEN(A1:A10),1/COUNTIF(A1:A10,A1:A10)))

or even simplier =COUNTIF(A1:B12,"<>""") 2) to find specific items,

then just use the COUNTIF formula.

Hope this helped, please check our exemples and templates.