1

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))
Philip
  • 189
  • 2
  • 10
  • 21
  • Im not sure about the unique values part, but to count around the blanks, you should use the formula: `=countif([range you need], "<>"&"")` this will count ever cell within the range that has some value in it – Kyle Tegt Feb 23 '15 at 21:16
  • possible duplicate of [Count unique values in a column in Excel](http://stackoverflow.com/questions/18586815/count-unique-values-in-a-column-in-excel) – zx8754 Feb 23 '15 at 21:21
  • If you use `SUMPRODUCT` in place of `SUM` the formula does not have to be entered as an array formula. –  Feb 23 '15 at 21:24

2 Answers2

6

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.

-1

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

if not blank cells:
 sum
Terry Jan Reedy
  • 18,414
  • 3
  • 40
  • 52
Kevin F
  • 169
  • 7
  • 17