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

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.

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