Excel计算值在一组列中显示的次数，但仅计算每行名称的一个实例 [英] Excel counts the number of times a value appears in a group of columns but counts only one instance of the name per row

问题描述

我需要找到一个名字出现在这个列表中的次数，但是我只想对每行排列一个名字的一个实例，而不会计算行中重复的名称。

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

在这个工作表中，我想计算Bill的名字的数量，得到2的答案，因为Bill的名字显示在两行中。在同样的方面，如果我选择数字Ted，答案应该是3，因为Ted的名字显示在三行。

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.

推荐答案

使用带SUMPRODUCT的OFFSET。在F7（每个提供的图像）as，

Use OFFSET with SUMPRODUCT. In F7 (per supplied image) as,

=SUMPRODUCT(SIGN(COUNTIF(OFFSET(B$1:E$1, ROW($1:$4)-1, 0), E7)))

SIGN函数将任何正数每行1个。 ROW（1：4）循环遍历每行。

The SIGN function turns any positive number to 1 for each row. ROW(1:4) cycles through each of the rows.

这篇关于Excel计算值在一组列中显示的次数，但仅计算每行名称的一个实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！