SUMIF being used properly?
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.
"Mike H" wrote:
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")*(D1:D6))
Mike
"frenchtoast" wrote:
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.
|