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!

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!

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

Or just:-

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------
It's easier to beg forgiveness than ask permission :-)



"andy62" wrote:

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!

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

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!