Excel counts the number of times a value appears in a group of columns but counts only one instance of the name per row

Question

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.

Answer 1

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

=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.

Answer 2

Assuming you put your name of choice, e.g. "Ted", in G1, array formula**:

=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).