I need to find the number of times a name appears on this list but I only want to count one instance of the name one time for each row without counting the duplicate name in the row.
我需要查找名称出现在此列表中的次数,但我只想为每行计算一次名称实例,而不计算行中的重复名称。
For instance: I have the following in a range...
例如:我在一个范围内有以下内容......
Red Bill Jack Ruby Bill
Blue Ruby Ivan Raul Ted
Green Ted James Rick Ted
Red Ted Phil Ruby Bill
And in this worksheet, I want to count the number of instances of the name Bill and get the answer of 2 because Bill's name shows up in two rows. In the same respect, If I choose to count the name Ted, the answer should be 3 because Ted's name shows up in three rows.
在这个工作表中,我想计算名称Bill的实例数,得到2的答案,因为Bill的名字显示在两行中。在同样的方面,如果我选择计算Ted的名字,答案应该是3,因为Ted的名字显示在三行中。
3 个解决方案
#1
#2
4
Assuming you put your name of choice, e.g. "Ted", in G1
, array formula**:
假设你选择了你的名字,例如“Ted”,在G1中,数组公式**:
=SUM(0+(MMULT(0+(A1:E4=G1),TRANSPOSE(COLUMN(A1:E4)))>0))
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
**数组公式的输入方式与“标准”公式的输入方式不同。您只需按住CTRL和SHIFT键,然后按ENTER键,而不是只按ENTER键。如果你已经正确地完成了它,你会注意到Excel在公式周围放置了大括号{}(尽管不要尝试自己手动插入这些)。
#3
#1
7
Use OFFSET with SUMPRODUCT. In F7 (per supplied image) as,
使用OFFSET与SUMPRODUCT。在F7(每个提供的图像)中,
=SUMPRODUCT(SIGN(COUNTIF(OFFSET(B$1:E$1, ROW($1:$4)-1, 0), E7)))
The SIGN function turns any positive number to 1 for each row. ROW(1:4)
cycles through each of the rows.
SIGN功能将每行的正数转为1。行(1:4)循环遍历每一行。
#2
4
Assuming you put your name of choice, e.g. "Ted", in G1
, array formula**:
假设你选择了你的名字,例如“Ted”,在G1中,数组公式**:
=SUM(0+(MMULT(0+(A1:E4=G1),TRANSPOSE(COLUMN(A1:E4)))>0))
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
**数组公式的输入方式与“标准”公式的输入方式不同。您只需按住CTRL和SHIFT键,然后按ENTER键,而不是只按ENTER键。如果你已经正确地完成了它,你会注意到Excel在公式周围放置了大括号{}(尽管不要尝试自己手动插入这些)。