SUMIF function being used properly?

[frenchtoast]

I am trying to pull data from one file to another.

For simplicity, my data includes multiple customers and multiple products:

Cust. group #/ Cust. Name / Product code / Quantity
001 / Bob / A / 10
001 / Bob / B / 5
001 / Bob / C / 20
001 / Ted / A / 35
001 / Ted / B / 30
001 / Ted / C / 15

I need to look up using a customer group number (contains multiple customers
belonging to one group), and also look up by product code, to provide the
total quantity of that product.

I am using the SUMIF function but for some reason it is returning only one
figure for everything.

There is another trick to this equation, I need to separate this by weeks
(ie. week 1, and week 2 --though I can separate this into separate worksheets
for simplicity - but an extra step).

Thank you.

 

[Luke M]

Sumproduct is what you are looking for.

Example to find quantity of group" 001", product code "A"

=SUMPRODUCT((A2:A5="001")*(C2:C5="A")*(D25))

Note that this will work if your group code is being entered as text. If its
actually a number(value) just formatted to have the leading zeros, remove the
quotation marks and change to just (A2:A5=1).

Or you could make it reference a cell, if you want to have a little more
control over what summation you want.

Your example has no information about weeks, so I cannot give you an
formula. But, if you include another column with week number, you could
simplay add that into the sumproduct equation. Hopefully it gives you some
ideas to the right way to go.

 

[Bernie Deitrick]

What you want to use is Pivot Tables. They allow you to group your data by
any parameter or, when using dates, by week, month, or year.

Here's a quick intro:

http://www.cpearson.com/excel/pivots.htm

HTH,
Bernie
MS Excel MVP

 

[frenchtoast]

Hi Luke, thanks. It is a little challenging expressing the problem in
messages, so I'll try to be more descriptive.

The table I outlined earlier is my data chart. The table where I want to
display the outcome is in another file and contains:

Cust. group # is in a cell separate from the table, and Product Code is in
the table with other content (ie. product description, target quantity etc).

So I've tried where

"129" = cust. group#
"6963" = product code
Column L = quantity

=SUMPRODUCT(('[Tracking.xls]DataWeek1'!$E:$E="129")*('[Tracking.xls]DataWeek1'!$H:$H="6963")*('[Tracking.xls]DataWeek1'!$L:$L))

then it returned #NUM! error.

I'm not sure if I'm referencing the right fields.

(The values were in general format, then I changed to number, which didn't
make a difference)

Any suggestions?

 

[Luke M]

SUMPRODUCT won't let you call out entire columns, unless you have 2007.
Just change it to some number range
=SUMPRODUCT(('[Tracking.xls]DataWeek1'!$E2:$E="5000")*('[Tracking.xls]DataWeek1'!$H3:$H="5000")*('[Tracking.xls]DataWeek1'!$L2:$L5000))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Hi Luke, thanks. It is a little challenging expressing the problem in
messages, so I'll try to be more descriptive.

The table I outlined earlier is my data chart. The table where I want to
display the outcome is in another file and contains:

Cust. group # is in a cell separate from the table, and Product Code is in
the table with other content (ie. product description, target quantity etc).

So I've tried where

"129" = cust. group#
"6963" = product code
Column L = quantity

=SUMPRODUCT(('[Tracking.xls]DataWeek1'!$E:$E="129")*('[Tracking.xls]DataWeek1'!$H:$H="6963")*('[Tracking.xls]DataWeek1'!$L:$L))

then it returned #NUM! error.

I'm not sure if I'm referencing the right fields.

(The values were in general format, then I changed to number, which didn't
make a difference)

Any suggestions?

Sumproduct is what you are looking for.

Example to find quantity of group" 001", product code "A"

=SUMPRODUCT((A2:A5="001")*(C2:C5="A")*(D25))

Note that this will work if your group code is being entered as text. If its
actually a number(value) just formatted to have the leading zeros, remove the
quotation marks and change to just (A2:A5=1).

Or you could make it reference a cell, if you want to have a little more
control over what summation you want.

Your example has no information about weeks, so I cannot give you an
formula. But, if you include another column with week number, you could
simplay add that into the sumproduct equation. Hopefully it gives you some
ideas to the right way to go.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*