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

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

Ron

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

"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.