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))
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.
You can use the if function to verify blank cells.
http://support.microsoft.com/kb/214244
if not blank cells:
sum
=countif([range you need], "<>"&"")this will count ever cell within the range that has some value in it – Kyle TegtSUMPRODUCTin place ofSUMthe formula does not have to be entered as an array formula. – user4039065