Wildcard character inside sumproduct

[andy62]

I need this component of my sumproduct function, "--(A1:A100=*Cindy Lou*)",
to return 1 when the string "Cindy Lou" is part of the cell in the range, and
0 when it is not. What I mean by "part of" is that the target cells in A1
thru A100 have multiple names separated by Alt-Enter. So if cell A1 has
three names separated by Alt-Enter and "Cindy Lou" is one of the three, I get
a 1 (True). I also need it to work if "Cindy Lou" is the only name in the
cell. I don't think it's as easy as using wildcard characters; can I do
something with the search or find functions?

Oh, and in the component I won't be quoting "Cindy Lou" specifically; it'll
be a variable (cell reference like D4).

Thanks!

 

[andy62]

The first one works great, thanks. The second one, I believe, is the
standard form for an exact match situation, not my "contains" case.

 

[Carim]

Hi,

Wildcard character won't work inside sumproduct ...
My recommendation would be to use Left() Mid() Right() functions
which do work inside sumproduct() functions ...

HTH
Carim

 

[Ken Wright]

Or just:-

=COUNTIF(A1:A100,"*cindy-lou*")

 

[Lori]

Try in the sumproduct formula: --ISNUMBER(SEARCH("Cindy Lou",A1:A100))