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

 

[Mike H]

Hi,

You lost me when referring to weeks, there are none in your sample data but
how about this. Sums by Cust Group & product code.

=SUMPRODUCT((A1:A6=1)*(C1:C6="A")*(D16))

Mike

 

[frenchtoast]

Thanks Mike. (didn't realize i posted this twice..)

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?

PS. as for the weeks, ie. week 1 would appear in my data table that I can
use in my reporting table (I'm reporting quantities by week). I can separate
the data by week into different worksheets but just thought there may be a
simpler way.

 

[Pete_UK]

Unless you are using Excel 2007, you cannot use full-column references
with SUMPRODUCT - use E1:E1000, H1:H1000 etc, (or whatever).

Hope this helps.

Pete

Thanks Mike.  (didn't realize i posted this twice..)

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]DataWee­k1'!$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?

PS. as for the weeks, ie. week 1 would appear in my data table that I can
use in my reporting table (I'm reporting quantities by week).  I can separate
the data by week into different worksheets but just thought there may be a
simpler way.

Hi,

You lost me when referring to weeks, there are none in your sample databut
how about this. Sums by Cust Group & product code.


"frenchtoast" wrote:

- Show quoted text -