Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wildcard character inside sumproduct
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wildcard character inside sumproduct
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wildcard character inside sumproduct
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT, COUNTIF and wildcard | Excel Worksheet Functions | |||
Wildcard Character in an Array Formula | Excel Discussion (Misc queries) | |||
How do you find and replace a Wildcard character in Excel? | Excel Discussion (Misc queries) | |||
How do I use the wildcard character and the NOT function? | Excel Worksheet Functions | |||
Sumproduct with Wildcard * | Excel Worksheet Functions |