Wildcard character inside sumproduct
The first one works great, thanks. The second one, I believe, is the
standard form for an exact match situation, not my "contains" case.
"Ron Coderre" wrote:
Try something like this:
For "contains"
=SUMPRODUCT(--ISNUMBER(SEARCH("cindy lou",A1:A100)))
or
For a NON-case sensitive match
=SUMPRODUCT(--(A1:A100="cindy lou"))
Does that help?
***********
Regards,
Ron
XL2002, WinXP
"andy62" wrote:
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!
|