Sumproduct...how to use *contain*?
 

Hi there,

Let's say I have the function

=SUMPRODUCT((A2:A10="Monday")*(C2:C10="Apples")*(D 2:D10))

I'm trying to use the SUMPRODUCT function like SUMIF(except for
multiple criteria).

Instead of using "Apples", is it possible to sum everything that
contains the term "Apples"?
BTW, "*Apples*" does not seem to work in this function.

Any ideas how this can be done?alternatives?

Thanks for your help


--
hkoros
------------------------------------------------------------------------
hkoros's Profile: http://www.excelforum.com/member.php...o&userid=24620
View this thread: http://www.excelforum.com/showthread...hreadid=382103

Try this:
=SUMPRODUCT((A2:A10="Monday")*(COUNTIF(C2:C10,"*Ap ples*")0)*(D2:D10))

Does that help?

Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=382103

It seems to multiply my results instead of adding like a SUMIF.


--
hkoros
------------------------------------------------------------------------
hkoros's Profile: http://www.excelforum.com/member.php...o&userid=24620
View this thread: http://www.excelforum.com/showthread...hreadid=382103

I don't understand how it could be multiplying your results.


The equation:
=SUMPRODUCT((A2:A10="Monday")*(COUNTIF(C2:C10,"*Ap ples*")0)*(D2:D10))

What that does is multiply the binary result of the 1st test (Test1)
timesthe binary result of the second test (Test2). That product is
multiplied times the amounts in column D. All of the products are then
added

+(Test1 x Test2 x Value)
+(Test1 x Test2 x Value)
+(Test1 x Test2 x Value)
------------------------------
sum of the above

for "Monday" "Apples and Pears" 10: you get 1x1x10=10

for "Monday" "Pears" 10: you get 1x0x10=0

The sumproduct will add all of the results, which will either be the
values or zeroes.

Hence, my confusion.

Am I missing something?

Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=382103

Try this:
=SUMPRODUCT((A2:A10="Monday")*NOT(ISERROR((SEARCH( "APPLES",C2:C10))))*(D2:D10))

Does that work?

Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=382103

Sometimes replacing "not(iserror(" with "isnumeric(" makes the formula easier to
read.

Ron Coderre wrote:

Try this:
=SUMPRODUCT((A2:A10="Monday")*NOT(ISERROR((SEARCH( "APPLES",C2:C10))))*(D2:D10))

Does that work?

Ron

--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=382103

--

Dave Peterson

Thanks for the reminder, Dave. I go back and forth on that one. Seems like
when I'm in a hurry, I forget the ISNUMBER function, even though that's my
preference, too.

Ron

Yup...I was missing something. Turned out to be a hardware problem.....a
loose nut in front of my keyboard. :\

Ron

"Dave Peterson" wrote...
Sometimes replacing "not(iserror(" with "isnumeric(" makes the
formula easier to read.

Ron Coderre wrote:
Try this:
=SUMPRODUCT((A2:A10="Monday")
*NOT(ISERROR((SEARCH("APPLES",C2:C10))))*(D2:D10 ))
....

Easier to read is good, but sometimes minimizing the number of function
calls is even better.

ISNUMBER(SEARCH("APPLES",C2:C10))

can be replaced by

SUBSTITUTE(C2:C10,"APPLES","")<C2:C10

This isn't necessary in this case, but there are times the 7 nested function
call limitation looms large.

Harlan Grove wrote:
"Dave Peterson" wrote...

Sometimes replacing "not(iserror(" with "isnumeric(" makes the
formula easier to read.

Ron Coderre wrote:

Try this:
=SUMPRODUCT((A2:A10="Monday")
*NOT(ISERROR((SEARCH("APPLES",C2:C10))))*(D2:D1 0))

...

Easier to read is good, but sometimes minimizing the number of function
calls is even better.

ISNUMBER(SEARCH("APPLES",C2:C10))

can be replaced by

SUBSTITUTE(C2:C10,"APPLES","")<C2:C10

This isn't necessary in this case, but there are times the 7 nested function
call limitation looms large.



Maybe MS should extend SUBSTITUTE with an optional arg: 0 (default) for
case-sensitive and 1 for case-insensitive substitutions.

Thanks for the help everyone


--
hkoros
------------------------------------------------------------------------
hkoros's Profile: http://www.excelforum.com/member.php...o&userid=24620
View this thread: http://www.excelforum.com/showthread...hreadid=382103

Aladin Akyurek wrote...
Harlan Grove wrote:
....
ISNUMBER(SEARCH("APPLES",C2:C10))

can be replaced by

SUBSTITUTE(C2:C10,"APPLES","")<C2:C10
....
Maybe MS should extend SUBSTITUTE with an optional arg: 0 (default) for
case-sensitive and 1 for case-insensitive substitutions.

I had meant to respond to this earlier in the week, but hadn't gotten
around to it until now. You should have been more explicit: I screwed
up.

The two expressions aren't identical. The SEARCH expression would
locate 'APPLES' or 'apples' or 'aPpLeS', etc., while SUBSTITUTE is
always case-sensitive, so it'd only locate 'APPLES'. The SUBSTITUTE
expression would need to use LOWER or UPPER to ensure case-insensitive
behavior, so no benefit. It only serves as a true replacement for

ISNUMBER(FIND("APPLES",C2:C10))