用空白计数独特值excel公式 [英] Count unique values excel formula with Blanks
问题描述
我正在使用公式来计算excel中的唯一值。我已经尝试使用下面的公式,但它返回#DIV!/ 0错误,因为我的范围内有空格。有没有办法通过使用公式来计算一个范围内的空白的唯一值?对于这个问题的任何帮助将不胜感激!
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))
推荐答案
单元格,采取公式并调整您的计数的分子唯一,以检查非空白,然后添加零长度字符串到 COUNTIF函数的标准参数。
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&))
检查分子中的非空白单元格意味着任何空白单元格都将返回零。无论分母是什么,任何分数为零的分数将为零。附加到COUNTIF的标准部分的空字符串足以避免#DIV / 0!错误。
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.
更多信息,请访问使用SUMPRODUCT()细分统计的唯一计数。
这篇关于用空白计数独特值excel公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!