vba 仅当 B 列中的 ID 重复时，如何计算 C 列中“是”的数量一次
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14608006/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to count number of "Yes" in column C once only if ID in column B repeats
提问by Mowgli
I need help counting Yes in column C, but only count it once if ID in column B repeats
我需要帮助计算 C 列中的 Yes，但如果 B 列中的 ID 重复，则只计算一次
Sample of output. for sample below count should be 6.
Here is dropbox file link.
Dropbox file link
输出样本。对于下面的示例，计数应为 6。
这是保管箱文件链接。
Dropbox 文件链接
I can do this already via if and else condition using formula, but I would like someone can come up with better way using single line formula.
我已经可以通过 if 和 else 条件使用公式来做到这一点，但我希望有人可以使用单行公式提出更好的方法。
Current solutionin cell F2
单元格 F2 中的当前解决方案
=IF(B2<>B1,IF(C2="Yes","1",""),IF(B2=B1,IF(F1="1","",IF(B2=#REF!,"",""))))
in cell F3 and down
在单元格 F3 和向下
=IF(B3<>B2,IF(C3="Yes","1",""),IF(B3=B2,IF(F2="1","",IF(B3=B1,"",""))))
Then I just count all ones and get total 1.
然后我只计算所有的并得到总数为 1。
=countif(F2:F159,"1")
回答by barry houdini
You can use a similar approach to my previous answer in your link, i.e. using cell refs in your dropbox example
您可以在链接中使用与我之前的答案类似的方法，即在 Dropbox 示例中使用单元格引用
=SUM(IF(FREQUENCY(IF(I2:I14="Yes",H2:H14),H2:H14),1))
=SUM(IF(FREQUENCY(IF(I2:I14="Yes",H2:H14),H2:H14),1))
confirmed with CTRL+SHIFT+ENTER
确认与 CTRL+SHIFT+ENTER
H2:H14 has to be numeric data
H2:H14 必须是数字数据
Generically, assuming Range 4 is numeric you can use this formula for up to 3 conditions
通常，假设范围 4 是数字，您可以将此公式用于最多 3 个条件
=SUM(IF(FREQUENCY(IF((Range1="x")*(Range2="y")*(Range3="z"),Range4),Range4),1))
=SUM(IF(FREQUENCY(IF((Range1="x")*(Range2="y")*(Range3="z"),Range4),Range4),1))
conditions can be added or removed as required
可以根据需要添加或删除条件
回答by Doug Glancy
Put this in D2 and drag down:
把它放在 D2 中并向下拖动：
=IF(COUNTIFS(B:B2,B2,C:C2,"Yes")=1,1,"don't count")
