Home |
Search |
Today's Posts |
#1
|
|||
|
|||
wildcard in sumproduct?
hi guys, i got a formula that ggoes like this = sumproduct(--(A1:A100="Apple"),--(B1:B100=Bear), --(problem_in here), D1:D100) In the problem_in_here criteria, it is a column with alot of description . But the key criteria i need is the key word "happy" with maybe at any part of a sentence. something like the use of a wildcard in countif (). IS that possibe? pls advise.... -- cjjoo ------------------------------------------------------------------------ cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916 View this thread: http://www.excelforum.com/showthread...hreadid=476740 |
#2
|
|||
|
|||
wildcard in sumproduct?
--ISNUMBER(SEARCH(SubString,Range))
cjjoo wrote: hi guys, i got a formula that ggoes like this = sumproduct(--(A1:A100="Apple"),--(B1:B100=Bear), --(problem_in here), D1:D100) In the problem_in_here criteria, it is a column with alot of description . But the key criteria i need is the key word "happy" with maybe at any part of a sentence. something like the use of a wildcard in countif (). IS that possibe? pls advise.... -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
#3
|
|||
|
|||
wildcard in sumproduct?
=sumproduct(--(A1:A100="Apple"),--(B1:B100=Bear), --(ISNUMBER(FIND("happy",C 1:C100))),D1:D100) if it is case sensitive =sumproduct(--(A1:A100="Apple"),--(B1:B100=Bear), --(ISNUMBER(SEARCH("happy" ,C1:C100))),D1:D100) if not -- HTH RP (remove nothere from the email address if mailing direct) "cjjoo" wrote in message ... hi guys, i got a formula that ggoes like this = sumproduct(--(A1:A100="Apple"),--(B1:B100=Bear), --(problem_in here), D1:D100) In the problem_in_here criteria, it is a column with alot of description . But the key criteria i need is the key word "happy" with maybe at any part of a sentence. something like the use of a wildcard in countif (). IS that possibe? pls advise.... -- cjjoo ------------------------------------------------------------------------ cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916 View this thread: http://www.excelforum.com/showthread...hreadid=476740 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Wildcard characters in sumproduct | Excel Worksheet Functions | |||
SUMPRODUCT with Wildcard | Excel Worksheet Functions | |||
Sumproduct Wildcard | Excel Discussion (Misc queries) | |||
Sumproduct with Wildcard * | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |