Counting Data that Contains Multiple Variables

[Legacy 313294]

Hi,

I have a list of terms (list a) and a list of the states (list b) and I want to write a formula that counts when one of the values in list a contains one of the values from list b

Example

[TABLE="width: 500"]
<tbody>[TR]
[TD]list a[/TD]
[TD]list b[/TD]
[/TR]
[TR]
[TD]cars to buy in florida[/TD]
[TD]florida[/TD]
[/TR]
[TR]
[TD]florida places to buy cars[/TD]
[TD]new york[/TD]
[/TR]
[TR]
[TD]new york car rental[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]buy cars new york[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Desired output

[TABLE="width: 500"]
<tbody>[TR]
[TD]Output[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]florida[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]new york[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

Normally I would just use =countif(A1, "*florida*") and sum the total, however there are >50 variables on list b and it would be very time consuming. I'm sure there is a way to do this more efficiently, but can't seem to figure it out.

Any help would be much appreciated!

 

[texasalynn]

not sure this is what you need
[TABLE="class: grid, width: 227"]
<tbody>[TR]
[TD="width: 212, bgcolor: white"]list a[/TD]
[TD="width: 90, bgcolor: white"]list b[/TD]
[/TR]
[TR]
[TD="width: 212, bgcolor: white"]cars to buy in florida[/TD]
[TD="width: 90, bgcolor: white"]florida[/TD]
[/TR]
[TR]
[TD="width: 212, bgcolor: white"]florida places to buy cars[/TD]
[TD="width: 90, bgcolor: white"]new york[/TD]
[/TR]
[TR]
[TD="width: 212, bgcolor: white"]new york car rental
[/TD]
[TD="width: 90, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="width: 212, bgcolor: white"]buy cars new york[/TD]
[TD="width: 90, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 212, bgcolor: white"]Output[/TD]
[TD="bgcolor: transparent"]Count[/TD]
[/TR]
[TR]
[TD="width: 212, bgcolor: white"]florida[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="width: 212, bgcolor: white"]new york[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[/TR]
</tbody>[/TABLE]

row example starts on row 15
array formula used:
=SUMPRODUCT(--(IFERROR(SEARCH(A21,$A$15:$A$18),0)>0))

formula is entered by pressing CTRL+SHIFT+ENTER, which adds the curly brackets.