A | B |

I like to go to texas and montana | texas and florida smell |

I am from florida | yay ohio is the best |

california has cows | newyork woohoo |

montana is awesome | texas is awesome too |

<tbody>

</tbody>

C | D |

texas | texas |

montana | ohio |

<tbody>

</tbody>

Using the example above (this is a grossly abridged version), i'm trying to do a modification on the COUNTIF(S) statement. C is criteria for A, and D is criteria for B.

Stepwise I'd like the function to think this way:

If A1 contains any in C OR B1 contains any in D, then count +1

if A2 contains any in C OR B2 contains any in D, then count +1

if A3 contains any in C OR B3 contains any in D, then count +1

if A4 contains any in C OR B4 contains any in D, then count +1

So while the number of CELLS that meet the criteria is 5, the number of lines that meet the criteria is 3. The long way that I've thought of doing this (below) is basically adding up all the cells individually, then subtracting those that are duplicates. However I have a lot more criteria and a lot more cells, so both the formula and the computing time would be exhaustive.

=countif(A:A,"*"&C1&"*")+countif(A:A,"*"&C2&"*")+countif(B:B,"*"&D1&"*")+countif(B:B,"*"&D2&"*")-countifs(A:A,"*"&C1&"*",A:A,"*"&C2&"*")-countifs(A:A,"*"&C1&"*",B:B,"*"&C1&"*")...........-countifs(A:A,"*"&C1&"*",A:A,"*"&C2&"*",B:B,"*"&D1&"*",B:B,"*"&D2&"*")