Count unique values excel formula with Blanks

Question

I am trying to count unique values in excel by using a formula. I have tried using the formula below but it returns a #DIV!/0 error because I have blanks in my range. Is there anyway to count unique values in a range with blanks by using a formula? Any help with this issue would be greatly appreciated!

=SUM(1/COUNTIF(Usage!J2:J6000,Usage!J2:J6000))

Answer 1

You can use the if function to verify blank cells. http://support.microsoft.com/kb/214244

if not blank cells:
 sum 

Answer 2

If you have to compensate for blank cells, take the formula and adjust the numerator of your count unique to check for non-blanks then add a zero-length string to the COUNTIF function's criteria argument.

=SUMPRODUCT((Usage!J2:J6000<>"")/COUNTIF(Usage!J2:J6000,Usage!J2:J6000&""))

Checking for non-blank cells in the numerator means that any blank cell will return a zero. Any fraction with a zero in its numerator will be zero no matter what the denominator is. The empty string appended to the criteria portion of the COUNTIF is sufficient to avoid #DIV/0! errors.

More information at Count Unique with SUMPRODUCT() Breakdown.