In column A I have list of different names. In column B, I have values either 0 or 1.
在A列我有不同名字的列表。在B列中，我有0或1的值。
I want to get a count of all the unique names from column A which have 1 in column B.
我想要得到a列中所有唯一的名字的计数，B列中有1。
Using below array formula I am able count unique names but not able to apply condition on column B.
使用下面的数组公式，我可以计算唯一的名称，但不能在B列上应用条件。
=SUM(1/COUNTIF(A:A,A:A))
5 个解决方案
#1
8
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:
假设不超过100行，尝试用这个“数组公式”来计算A2中的不同名称:A100，在相同的一行中有一个1，在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))
=总和(如果(频率(如果(B2:B100 = 1,如果(A2:A100 < > ",匹配(A2:A100 A2:A100,0)))、行(A2:A100)行(A2)+ 1),1))
confirmed with CTRL+SHIFT+ENTER
确认与CTRL + SHIFT + ENTER
Note that I say different not unique as the two are not the same
注意，我说的不同，不是唯一的，因为两者是不一样的
#2
6
Like this?
像这样的吗?
=SUM(--(FREQUENCY(IF(($B$1:$B$8=1), COUNTIF($A$1:$A$8, "<"&$A$1:$A$8), ""), COUNTIF($A$1:$A$8, "<"&$A$1:$A$8))>0)))
=((频率和(如果(($ B $ 1:$ B 8美元= 1),条件统计(1美元:8美元,美元“<”& $ $ 1:$ 8美元),“”),条件统计(1美元:8美元,美元" < " & $ $ 1:$ 8美元))> 0)))
This is an array formula. You will have to press CTL + SHIFT + ENTER
这是一个数组公式。你必须按CTL + SHIFT + ENTER键
Screenshot
截图
#3
3
Does it have to be a formula? A really easy way to do this is to may a pivot table out of columns A and B. Then set Column B as the filter field and Count of A as the values (you need to label columns A and B). Then A4 on the pivot sheet (at least in the default) should contain your answer. This would work for any size list and indeed would work when there are multiple conditions.
它一定是一个公式吗?一个非常简单的方法是可能的数据透视表列A和B,然后设置列B一样的过滤字段和计数的值(您需要标签列A和B)。然后在枢轴表A4(至少在默认)应该包含你的答案。这适用于任何大小列表，并且在有多个条件时确实适用。
If it has to be a formula, does it have to be entirely so? If you are allowed to sort by A before your formula works, then how about this workflow. I'm not crazy about this and I suspect it could be improved.
如果它必须是一个公式，它必须完全是这样吗?如果在你的公式工作之前允许你用A排序，那么这个工作流呢?我不喜欢这个，我怀疑它可以改进。
1) Sort by A (ascending or descending ), then by B (descending)
1)按A(升序或降序)排序，然后按B(降序)排序
2) In C each row of C put a formula like this:
2)在C中，C的每一行都有这样的公式:
=if (and(A2<>A1,B2=1),1,0)
C1 would be as follows:
C1为:
=b1
Drag from C2 until the last row of your data (say c500).
从C2拖到数据的最后一行(例如c500)。
3) Then count by summing C, =sum(c1:c500).
然后用C求和，=sum(c1:c500)。
You can certainly can drag the formula below the rows of data (as long as you know the maximum number you'll need), it should return 0 for all the blank rows. Then as you add data to A and B the rest will update automatically. You will likely need to to resort the data too.
您当然可以将公式拖到数据行的下面(只要您知道需要的最大数量)，它应该为所有的空行返回0。然后，当您向A和B添加数据时，其余的将自动更新。你可能也需要利用这些数据。
One nice thing about the pivot table solution is that it can grow automatically with your data set as long as you insert the new data within the rows of the existing data. No sorting required of course.
pivot表解决方案的一个优点是，只要在现有数据的行中插入新数据，它就可以随数据集自动增长。当然不需要排序。
#4
3
A relatively simple solution to this common problem is
这个常见问题的一个相对简单的解决方案是
=SUM((B:B=1)/COUNTIFS(A:A,A:A,B:B,B:B))
=和((B:B = 1)/条件统计(答:,:A、B:B,B:B))
entered as an array formula.
作为数组公式输入。
You will need to limit the size of the arrays to where you actually have data.
您需要将数组的大小限制在实际拥有数据的地方。
#5
1
Easy with a PivotTable ......:
容易的数据透视表……
#1
8
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:
假设不超过100行，尝试用这个“数组公式”来计算A2中的不同名称:A100，在相同的一行中有一个1，在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))
=总和(如果(频率(如果(B2:B100 = 1,如果(A2:A100 < > ",匹配(A2:A100 A2:A100,0)))、行(A2:A100)行(A2)+ 1),1))
confirmed with CTRL+SHIFT+ENTER
确认与CTRL + SHIFT + ENTER
Note that I say different not unique as the two are not the same
注意，我说的不同，不是唯一的，因为两者是不一样的
#2
6
Like this?
像这样的吗?
=SUM(--(FREQUENCY(IF(($B$1:$B$8=1), COUNTIF($A$1:$A$8, "<"&$A$1:$A$8), ""), COUNTIF($A$1:$A$8, "<"&$A$1:$A$8))>0)))
=((频率和(如果(($ B $ 1:$ B 8美元= 1),条件统计(1美元:8美元,美元“<”& $ $ 1:$ 8美元),“”),条件统计(1美元:8美元,美元" < " & $ $ 1:$ 8美元))> 0)))
This is an array formula. You will have to press CTL + SHIFT + ENTER
这是一个数组公式。你必须按CTL + SHIFT + ENTER键
Screenshot
截图
#3
3
Does it have to be a formula? A really easy way to do this is to may a pivot table out of columns A and B. Then set Column B as the filter field and Count of A as the values (you need to label columns A and B). Then A4 on the pivot sheet (at least in the default) should contain your answer. This would work for any size list and indeed would work when there are multiple conditions.
它一定是一个公式吗?一个非常简单的方法是可能的数据透视表列A和B,然后设置列B一样的过滤字段和计数的值(您需要标签列A和B)。然后在枢轴表A4(至少在默认)应该包含你的答案。这适用于任何大小列表，并且在有多个条件时确实适用。
If it has to be a formula, does it have to be entirely so? If you are allowed to sort by A before your formula works, then how about this workflow. I'm not crazy about this and I suspect it could be improved.
如果它必须是一个公式，它必须完全是这样吗?如果在你的公式工作之前允许你用A排序，那么这个工作流呢?我不喜欢这个，我怀疑它可以改进。
1) Sort by A (ascending or descending ), then by B (descending)
1)按A(升序或降序)排序，然后按B(降序)排序
2) In C each row of C put a formula like this:
2)在C中，C的每一行都有这样的公式:
=if (and(A2<>A1,B2=1),1,0)
C1 would be as follows:
C1为:
=b1
Drag from C2 until the last row of your data (say c500).
从C2拖到数据的最后一行(例如c500)。
3) Then count by summing C, =sum(c1:c500).
然后用C求和，=sum(c1:c500)。
You can certainly can drag the formula below the rows of data (as long as you know the maximum number you'll need), it should return 0 for all the blank rows. Then as you add data to A and B the rest will update automatically. You will likely need to to resort the data too.
您当然可以将公式拖到数据行的下面(只要您知道需要的最大数量)，它应该为所有的空行返回0。然后，当您向A和B添加数据时，其余的将自动更新。你可能也需要利用这些数据。
One nice thing about the pivot table solution is that it can grow automatically with your data set as long as you insert the new data within the rows of the existing data. No sorting required of course.
pivot表解决方案的一个优点是，只要在现有数据的行中插入新数据，它就可以随数据集自动增长。当然不需要排序。
#4
3
A relatively simple solution to this common problem is
这个常见问题的一个相对简单的解决方案是
=SUM((B:B=1)/COUNTIFS(A:A,A:A,B:B,B:B))
=和((B:B = 1)/条件统计(答:,:A、B:B,B:B))
entered as an array formula.
作为数组公式输入。
You will need to limit the size of the arrays to where you actually have data.
您需要将数组的大小限制在实际拥有数据的地方。
#5
1
Easy with a PivotTable ......:
容易的数据透视表……