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

list a	list b
cars to buy in florida	florida
florida places to buy cars	new york
new york car rental
buy cars new york

<tbody>
</tbody>

Desired output

Output	Count
florida	2
new york	2

<tbody>
</tbody>

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

list a	list b
cars to buy in florida	florida
florida places to buy cars	new york
new york car rental
buy cars new york
Output	Count
florida	2
new york	2

<tbody>
</tbody>

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.