SUMPRODUCT double counting

[fb7894]

Is there a way to use the SUMPRODUCT formula with 'OR' conditions and suppress the "double counting'?
Example. See attached worksheet. I want to count the number of rows where Mike is a member of the Bulls. 'Mike' can occur anywhere in columns B:E.
My SUMPRODUCT formula, however, is double counting each instance where Mike occurs in multiple columns. My formula in C16 returns 6. Should be 2.

[GTO]

Try:

=SUMPRODUCT(--(A2:A6="Bulls"),--((B2:B6="Mike")+(C2:C6="Mike")+(D2:D6="Mike")+(E2:E6="Mike")>0))

...compliments of Bob Phillips if it works, shame on me if it does not, but I am pretty sure I have it correct.

For an example, see: 3.4.1 Counting OR Conditions

in: SUMPRODUCT Step By Step - Part 3

Hope that helps,

Mark