Sumproduct/Countif Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a list of values (some blanks) in j3:j8.
I've got a list of cells with text in them (Z9:AH9).

I want to find out if any of the cells in Z9:AH9 contain
any of the text in J3:J8.

If I had only one cell, I'd do something like:

=if(countif(z9:ah9,"*"&j3&"*")>0,"Yes","No")

Is there a way I can generalize this for multiple cells?

Thanks for any help.
 
Hi
try the following array formula (to be entered with CTRL+SHIFT+ENTER)
to get the number of matches betwenn both lists:
=SUMPRODUCT(--(Z9:AH9=TRANSPOSE(J3:J8)))
 
Back
Top