Sumproduct...how to use *contain*?

[hkoros]

Hi there,

Let's say I have the function

=SUMPRODUCT((A2:A10="Monday")*(C2:C10="Apples")*(D210))

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

 

[Ron Coderre]

Try this:
=SUMPRODUCT((A2:A10="Monday")*(COUNTIF(C2:C10,"*Apples*")>0)*(D210))

Does that help?

Ron

 

[hkoros]

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

 

[Ron Coderre]

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


The equation:
=SUMPRODUCT((A2:A10="Monday")*(COUNTIF(C2:C10,"*Apples*")>0)*(D210))

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]

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

Does that work?

Ron

 

[Dave Peterson]

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

 

[Ron Coderre]

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

 

[Ron Coderre]

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

Ron

 

[Harlan Grove]

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

....

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.

 

[Aladin Akyurek]

...

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.

 

[hkoros]

Thanks for the help everyon

 

[Harlan Grove]

Aladin Akyurek wrote...

Harlan Grove wrote: .... ....
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))