Q:

Count unique values excel formula with Blanks

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

A:

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.

