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
{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://img.manongdao.com/sparkler-677774__340.jpg', '推荐 女痞 的文章《Count unique values excel formula with Blanks》','https://www.manongdao.com/article-422083.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}