Using COUNTIFS When Value Can Appear in Multiple Columns

[evanmer]

I'm trying to use a COUNTIFS function that allows me to say if a value (such as "Red") is in either column A or B, count the number of times a specific value (such as "Yes") appears in column C for each row without counting the same specific value ("Yes") twice. I can create a function that can give me the value for when A and B, or A and C are true, but am having trouble with (A or B) and C are true without over counting.

A..........B..........C
Red.......Blue......Yes
Green....Red.......Yes
Blue......Green.....No
Red.......Red.......Yes

Would result in:
Red & Yes: 2
Blue & Yes: 1
Green & Yes: 1

Any help is appreciated!! Thank you!

Evan

 

[RonB1111]

In your sample data, "Red & Yes" would be 3 if I'm understanding your request. If the desired result is really 2, ignore my solution below and please explain which 2 of your 4 rows are included and why the other 2 are excluded.

In cells E1 to E3, enter Red, Blue and Green respectively.
In cells F1 to F3, enter Yes in each cell.
Now enter this formula in G1 and copy down:

=SUMPRODUCT(SIGN((A$1:A$4=E1)+(B$1:B$4=E1)),--(C$1:C$4=F1))

 

[evanmer]

Hey Ron! Thanks for the quick feedback. You are correct and it was in fact supposed to be 3 rather than 2. Your solution worked great, thank you!

Before you answered, I was also able to get a different solution that worked for me as well.
D1:D4 I concatenated A and B
E1:E3 I did the same as you
F1 I entered:

=COUNTIFS(D:D,"*"&E1&"*",C:C, "Yes")

Yours saves a bit of work, so I like it a little more...thanks!

 

[RonB1111]

You're welcome!