SUMPRODUCT help

  • Thread starter Thread starter nlibeu
  • Start date Start date
N

nlibeu

Hello:

My question is if I have 3 or 4 criteria that need to be met before
excel acknowledges the row and then once it does it will sum the
accepted rows and return that sum into another sheet.

Category Price Date Customer #
InorgStock $30.00 10/20/2005101643
Inorg CB $415.80 1/23/2006 109597
InorgStock $15.00 1/23/2006 101643
Matrix Mod $50.00 1/23/2006 101643


If I want excel to only add up the extended price for the columns that
met the criteria Customer Number=101643 and Category=inorgstock and
Category=Inorg CB and Category=Matrix Mod and Document Date is
=10/20/05 but <=6/8/06, how would I accomplish that?
Click to expand...
I have tried sumproduct where SUMPRODUCT((C:C=Inorgstock)*(C:C=Matrix
Mod)*(C:C=Inorg CB)*(H:H=101643)*(E:E) and usually the error NUM
returns instead of $95. Is this to much to ask of excel?

Thank you for any help,

Nate
 
=SUMPRODUCT(((C2:C500="InorgStock")+(C2:C500="Matrix Mod")+(C2:C500="Inorg
CB"))*(D2:D500>=DATEVALUE("10/20/2005"))*(D2:D500<=DATEVALUE("6/8/06"))*(H2:H500=101643)*(E2:E500))

Of course, if you put this on another sheet, any range address needs to be preceeded by 'sheet
name'!


HTH,
Bernie
MS Excel MVP
 
Hi Bernie,

First let me thank you for the help.

I put a similar formula in =SUMPRODUCT(((Sheet1!C2:C1000="InorgStock")+
(Sheet1!C2:C1000="Matrix Mod")+(Sheet1!C2:C1000="InorgCB"))*(Sheet1!
K2:K1000=100094)*(Sheet1!G2:G1000))

I took out the dates to make it easier and just to check to see if it
would work. I got a value error in return. Do you know why this is
happening? I don’t know if I am asking to much of excel because I
can’t seem to get any formula to do all the steps and work. It sounds
simple: if all criteria matches, then sum the corresponding prices and
only those prices.

Thank you for any ideas,

Nate
 
Nate,

You cannot have an error in any of the ranges used for comparisons - that is
the likely cause of problems.

A good way to error check is to break your sumproduct down into parts like
this:

=SUMPRODUCT((Sheet1!C2:C1000="InorgStock")*1)
=SUMPRODUCT((Sheet1!C2:C1000="Matrix Mod")*1)
=SUMPRODUCT((Sheet1!C2:C1000="InorgCB")*1)
=SUMPRODUCT((Sheet1!K2:K1000=100094)*1)
=SUMPRODUCT((Sheet1!G2:G1000)*1)

If any of those return an error or a count less than you expected, that is
the range or value you should concentrate on.

Or, start with a completely blank sheet1, and the formula should return 0,
if all syntax for the formula is OK.

An even better way to approach this type of problem is to use a pivot
table. It will allow you to set the parameters by choosing which field
values are shown, and you never need to worry about formula syntax....

HTH,
Bernie
MS Excel MVP


Hi Bernie,

First let me thank you for the help.

I put a similar formula in =SUMPRODUCT(((Sheet1!C2:C1000="InorgStock")+
(Sheet1!C2:C1000="Matrix Mod")+(Sheet1!C2:C1000="InorgCB"))*(Sheet1!
K2:K1000=100094)*(Sheet1!G2:G1000))

I took out the dates to make it easier and just to check to see if it
would work. I got a value error in return. Do you know why this is
happening? I don’t know if I am asking to much of excel because I
can’t seem to get any formula to do all the steps and work. It sounds
simple: if all criteria matches, then sum the corresponding prices and
only those prices.

Thank you for any ideas,

Nate
 
Nate,

You cannot have an error in any of the ranges used for comparisons - thatis
the likely cause of problems.

A good way to error check is to break your sumproduct down into parts like
this:

=SUMPRODUCT((Sheet1!C2:C1000="InorgStock")*1)
=SUMPRODUCT((Sheet1!C2:C1000="Matrix Mod")*1)
=SUMPRODUCT((Sheet1!C2:C1000="InorgCB")*1)
=SUMPRODUCT((Sheet1!K2:K1000=100094)*1)
=SUMPRODUCT((Sheet1!G2:G1000)*1)

If any of those return an error or a count less than you expected, that is
the range or value you should concentrate on.

Or, start with a completely blank sheet1, and the formula should return 0,
if all syntax for the formula is OK.

An even better way  to approach this type of problem is to use a pivot
table.  It will allow you to set the parameters by choosing which field
values are shown, and you never need to worry about formula syntax....

HTH,
Bernie
MS Excel MVP


Hi Bernie,

First let me thank you for the help.

I put a similar formula in =SUMPRODUCT(((Sheet1!C2:C1000="InorgStock")+
(Sheet1!C2:C1000="Matrix Mod")+(Sheet1!C2:C1000="InorgCB"))*(Sheet1!
K2:K1000=100094)*(Sheet1!G2:G1000))

I took out the dates to make it easier and just to check to see if it
would work.  I got a value error in return.  Do you know why this is
happening?  I don’t know if I am asking to much of excel because I
can’t seem to get any formula to do all the steps and work.  It sounds
simple: if all criteria matches, then sum the corresponding prices and
only those prices.

Thank you for any ideas,

Nate
Click to expand...

Hey Bernie,

I performed the tasks you said individually and found the problems.
Now I can get the formula to work; however, I want excel to sum the
category if it is any of the criteria not if it is all words strung
together. So I am saying sum if InorgStock and/or sum if Matrix Mod
and/or Also sum if Inorg CB and only sum the corresponding values in
the extended price. Instead excel only sums if the category has all
three in the name and that is not correct. I even tried breaking it
apart and still having issues. Maybe if there is an “or” symbol?

=SUMPRODUCT((Sheet1!C1:C1000="InorgStock")*(Sheet1!G1:G1000))+((Sheet1!
C1:C1000="Matrix Mod")*(Sheet1!G1:G1000))+((Sheet1!C1:C1000="Inorg CB")
*(Sheet1!G1:G1000))

The above wouldn’t pick up the Inorg CB but it would total the
Inorgstock. And if I put in the company number it sums the whole
extended price column associated with the company number not the
products individually. Is there something else I can try or is it
time for the table?

Thanks,

Nate
 
You need to include all the allowable values inside one level of parens,
using addition: you had it correct a couple of posts ago...

=SUMPRODUCT(((Sheet1!C1:C1000="InorgStock") + (Sheet1! C1:C1000="Matrix
Mod") + (Sheet1!C1:C1000="Inorg CB"))*(Sheet1!G1:G1000))

HTH,
Bernie
MS Excel MVP



Nate,

You cannot have an error in any of the ranges used for comparisons - that
is
the likely cause of problems.

A good way to error check is to break your sumproduct down into parts like
this:

=SUMPRODUCT((Sheet1!C2:C1000="InorgStock")*1)
=SUMPRODUCT((Sheet1!C2:C1000="Matrix Mod")*1)
=SUMPRODUCT((Sheet1!C2:C1000="InorgCB")*1)
=SUMPRODUCT((Sheet1!K2:K1000=100094)*1)
=SUMPRODUCT((Sheet1!G2:G1000)*1)

If any of those return an error or a count less than you expected, that is
the range or value you should concentrate on.

Or, start with a completely blank sheet1, and the formula should return 0,
if all syntax for the formula is OK.

An even better way to approach this type of problem is to use a pivot
table. It will allow you to set the parameters by choosing which field
values are shown, and you never need to worry about formula syntax....

HTH,
Bernie
MS Excel MVP


Hi Bernie,

First let me thank you for the help.

I put a similar formula in =SUMPRODUCT(((Sheet1!C2:C1000="InorgStock")+
(Sheet1!C2:C1000="Matrix Mod")+(Sheet1!C2:C1000="InorgCB"))*(Sheet1!
K2:K1000=100094)*(Sheet1!G2:G1000))

I took out the dates to make it easier and just to check to see if it
would work. I got a value error in return. Do you know why this is
happening? I don’t know if I am asking to much of excel because I
can’t seem to get any formula to do all the steps and work. It sounds
simple: if all criteria matches, then sum the corresponding prices and
only those prices.

Thank you for any ideas,

Nate
Click to expand...

Hey Bernie,

I performed the tasks you said individually and found the problems.
Now I can get the formula to work; however, I want excel to sum the
category if it is any of the criteria not if it is all words strung
together. So I am saying sum if InorgStock and/or sum if Matrix Mod
and/or Also sum if Inorg CB and only sum the corresponding values in
the extended price. Instead excel only sums if the category has all
three in the name and that is not correct. I even tried breaking it
apart and still having issues. Maybe if there is an “or” symbol?

=SUMPRODUCT((Sheet1!C1:C1000="InorgStock")*(Sheet1!G1:G1000))+((Sheet1!
C1:C1000="Matrix Mod")*(Sheet1!G1:G1000))+((Sheet1!C1:C1000="Inorg CB")
*(Sheet1!G1:G1000))

The above wouldn’t pick up the Inorg CB but it would total the
Inorgstock. And if I put in the company number it sums the whole
extended price column associated with the company number not the
products individually. Is there something else I can try or is it
time for the table?

Thanks,

Nate
 
Slightly shorter

=SUM(SUMIF(C1:C1000,{"InorgSTock","Matrix Mod","Inorg CB"},G1:G1000))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Bob,

That is shorter, but the OP has additional limits that he wants to add in,
so SUMIF would not work.

Bernie
 
I just shortened what you had, SUMIF seemed appropriate for that. If the OP
wants more conditons he can use

=SUMPRODUCT((C1:C1000={"InorgSTock","Matrix Mod","Inorg CB"})*(G1:G1000))

which extends quite easily.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top