Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF being used properly?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF being used properly?
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF being used properly?
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 On Dec 5, 8:12*pm, frenchtoast wrote: 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. "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.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - sumif not updating properly | Excel Discussion (Misc queries) | |||
F2 key is not functioning properly | Excel Discussion (Misc queries) | |||
3d reference not working properly | Excel Worksheet Functions | |||
But not working properly | Excel Discussion (Misc queries) | |||
Does not add up properly | Excel Discussion (Misc queries) |