Count total rows in multiple columns with input values from list

[Flyboy123]

Hello,

I'm struggling to determine how to count the total number of rows (from a range of 2 columns) that contain entries from a list of strings.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Row
[/TD]
[TD]Col A
[/TD]
[TD]Col B
[/TD]
[TD] Col C
[/TD]
[TD]
[/TD]
[TD]String1
[/TD]
[TD]String2
[/TD]
[TD]String3
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]Fun
[/TD]
[TD="align: center"]No Fun
[/TD]
[TD="align: center"]Yes
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Odd
[/TD]
[TD="align: center"]Fun
[/TD]
[TD="align: center"]Confused
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]Fun
[/TD]
[TD="align: center"]Fun
[/TD]
[TD="align: center"]Yes
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]No Fun
[/TD]
[TD="align: center"]No Fun
[/TD]
[TD="align: center"]Yes
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]No Fun
[/TD]
[TD="align: center"]Confused
[/TD]
[TD="align: center"]Yes
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]No Fun
[/TD]
[TD="align: center"]Fun
[/TD]
[TD="align: center"]No
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

What I need from this is to look in Col A and in Col B and return the total number of rows that contain any of String1, String2, or String3 - however only count those where Col C is "Yes". In this case the answer should be 3 (i.e. Rows 1, 2, and 4 are good).

I appreciate your help as I'm sure you'll prove that the past 3 hours of trying to figure this out myself was not as productive as it could have been .

 

[Eric W]

Maybe:

	A	B	C	D	E	F	G	H

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Fun[/TD]
[TD="align: center"]No Fun[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Odd[/TD]
[TD="align: center"]Fun[/TD]
[TD="align: center"]Confused[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Fun[/TD]
[TD="align: center"]Fun[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]No Fun[/TD]
[TD="align: center"]No Fun[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]No Fun[/TD]
[TD="align: center"]Confused[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]No Fun[/TD]
[TD="align: center"]Fun[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]{=SUMPRODUCT(--(C1:C5="Yes"),--(MMULT(ISNUMBER(SEARCH(E1:G1,A1:A5&B1:B5))+0,{1;1;1})>0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

My formula returns a value of 4, since row 3 does contain the word fun. I can only assume that if there's a "No" in front of it, you don't want to count it. If that's the case, let me know how to identify that, and I'll adjust the formula.

 

[Flyboy123]

WOW!!! Excellent - worked like a charm! Bonus points to you for not only providing a great solution but for understanding my crap example! I should not have used 'No Fun' ... don't need that... what you've given me works perfectly.

You know, you spend a lot of time in Excel and you think you know something and then you get a solution like this and you realize that in fact you know very little... well back to being humble once again I go.

Many thanks,
Jeff