我想从列A中有1的列A中的所有唯一名称
使用下面的数组公式我可以计数唯一的名称,但无法在列B上应用条件。
= SUM(1 / COUNTIF(A:A,A:A))解决方案
假设不超过100行,尝试这个数组公式来计算A2:A100中的不同名称在B2中的同一行中为1:B100:
= SUM(IF(FREQUENCY(IF(B2:B100 = 1,IF :A100<> 中,MATCH(A2:A100,A2:A100,0))),ROW(A2:A100)-ROW(A2)1),1))
用确认CTRL + SHIFT + ENTER
请注意我说不同不一样,因为两者不一样
In column A I have list of different names. In column B, I have values either 0 or 1.
I want to get a count of all the unique names from column A which have 1 in column B.
Using below array formula I am able count unique names but not able to apply condition on column B.
=SUM(1/COUNTIF(A:A,A:A))解决方案
Assuming no more than 100 rows try this "array formula" to count the different names in A2:A100 where there is a 1 in the same row in B2:B100:
=SUM(IF(FREQUENCY(IF(B2:B100=1,IF(A2:A100<>"",MATCH(A2:A100,A2:A100,0))),ROW(A2:A100)-ROW(A2)+1),1))
confirmed with CTRL+SHIFT+ENTER
Note that I say different not unique as the two are not the same
更多推荐
计数具有条件的唯一值
发布评论