Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUNIF with 2 criteria cells
A B C D E F
1/06 2/06 3/06 4/06 1 ford 2 chey 3 dodge OK, on another sheet I have 20,900 lines of items I have sold to these customers. I have the cust name in column A, the date as listed in row 2 is in column E, and the number of pcs sold is in column C. I want to get the total amount sold to each cust during each month. Any help would be great. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUNIF with 2 criteria cells
Hi
One way, in cell B2 of Sheet2 enter =SUMPRODUCT(--(Sheet1!$A$2:$A$20900)=A2),--(Sheet!$E$2:$E$20900=B$1),$C2:$C20900) Copy across for the required number of columns. Copy the set of formulae down for the range of Values in column A. Better still would be to use a Pivot table on your data on Sheet1. For help on setting up Pivot tables take a look at http://peltiertech.com/Excel/Pivots/pivotstart.htm and http://www.contextures.com/xlPivot02.html -- Regards Roger Govier "Cube Farmer" wrote in message ... A B C D E F 1/06 2/06 3/06 4/06 1 ford 2 chey 3 dodge OK, on another sheet I have 20,900 lines of items I have sold to these customers. I have the cust name in column A, the date as listed in row 2 is in column E, and the number of pcs sold is in column C. I want to get the total amount sold to each cust during each month. Any help would be great. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUNIF with 2 criteria cells
Roger,
Thanks for the attempt. I couldn't get it to work though( i'm sure it's my doing) =SUMPRODUCT(--(Sheet3!$A$2:$A$20903)=A5)--(Sheet3!$G$2:$G$20903=$B$4)*(Sheet3!$E2:$E20903) That is what it ended up as when MS put in its fix. I changed some of the row and column #s to fit actual. On your formula the last part didn't call out another sheet. Is this correct? Also on the middle section it called out only "Sheet" without a #. Any further options? Thanks, Mike "Roger Govier" wrote: Hi One way, in cell B2 of Sheet2 enter =SUMPRODUCT(--(Sheet1!$A$2:$A$20900)=A2),--(Sheet!$E$2:$E$20900=B$1),$C2:$C20900) Copy across for the required number of columns. Copy the set of formulae down for the range of Values in column A. Better still would be to use a Pivot table on your data on Sheet1. For help on setting up Pivot tables take a look at http://peltiertech.com/Excel/Pivots/pivotstart.htm and http://www.contextures.com/xlPivot02.html -- Regards Roger Govier "Cube Farmer" wrote in message ... A B C D E F 1/06 2/06 3/06 4/06 1 ford 2 chey 3 dodge OK, on another sheet I have 20,900 lines of items I have sold to these customers. I have the cust name in column A, the date as listed in row 2 is in column E, and the number of pcs sold is in column C. I want to get the total amount sold to each cust during each month. Any help would be great. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUNIF with 2 criteria cells
Try this, DON'T let excel try to change it
=SUMPRODUCT(--(Sheet3!$A$2:$A$20903=A5),--(Sheet3!$G$2:$G$20903=$B$4),Sheet3!$E2:$E20903) will lookup A5 in the A range where the G range is equal to B4 then sum those conditions in E -- Regards, Peo Sjoblom Portland, Oregon "Cube Farmer" wrote in message ... Roger, Thanks for the attempt. I couldn't get it to work though( i'm sure it's my doing) =SUMPRODUCT(--(Sheet3!$A$2:$A$20903)=A5)--(Sheet3!$G$2:$G$20903=$B$4)*(Sheet3!$E2:$E20903) That is what it ended up as when MS put in its fix. I changed some of the row and column #s to fit actual. On your formula the last part didn't call out another sheet. Is this correct? Also on the middle section it called out only "Sheet" without a #. Any further options? Thanks, Mike "Roger Govier" wrote: Hi One way, in cell B2 of Sheet2 enter =SUMPRODUCT(--(Sheet1!$A$2:$A$20900)=A2),--(Sheet!$E$2:$E$20900=B$1),$C2:$C20900) Copy across for the required number of columns. Copy the set of formulae down for the range of Values in column A. Better still would be to use a Pivot table on your data on Sheet1. For help on setting up Pivot tables take a look at http://peltiertech.com/Excel/Pivots/pivotstart.htm and http://www.contextures.com/xlPivot02.html -- Regards Roger Govier "Cube Farmer" wrote in message ... A B C D E F 1/06 2/06 3/06 4/06 1 ford 2 chey 3 dodge OK, on another sheet I have 20,900 lines of items I have sold to these customers. I have the cust name in column A, the date as listed in row 2 is in column E, and the number of pcs sold is in column C. I want to get the total amount sold to each cust during each month. Any help would be great. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUNIF with 2 criteria cells
Thanks Peo,
That did the trick!! Mike "Peo Sjoblom" wrote: Try this, DON'T let excel try to change it =SUMPRODUCT(--(Sheet3!$A$2:$A$20903=A5),--(Sheet3!$G$2:$G$20903=$B$4),Sheet3!$E2:$E20903) will lookup A5 in the A range where the G range is equal to B4 then sum those conditions in E -- Regards, Peo Sjoblom Portland, Oregon "Cube Farmer" wrote in message ... Roger, Thanks for the attempt. I couldn't get it to work though( i'm sure it's my doing) =SUMPRODUCT(--(Sheet3!$A$2:$A$20903)=A5)--(Sheet3!$G$2:$G$20903=$B$4)*(Sheet3!$E2:$E20903) That is what it ended up as when MS put in its fix. I changed some of the row and column #s to fit actual. On your formula the last part didn't call out another sheet. Is this correct? Also on the middle section it called out only "Sheet" without a #. Any further options? Thanks, Mike "Roger Govier" wrote: Hi One way, in cell B2 of Sheet2 enter =SUMPRODUCT(--(Sheet1!$A$2:$A$20900)=A2),--(Sheet!$E$2:$E$20900=B$1),$C2:$C20900) Copy across for the required number of columns. Copy the set of formulae down for the range of Values in column A. Better still would be to use a Pivot table on your data on Sheet1. For help on setting up Pivot tables take a look at http://peltiertech.com/Excel/Pivots/pivotstart.htm and http://www.contextures.com/xlPivot02.html -- Regards Roger Govier "Cube Farmer" wrote in message ... A B C D E F 1/06 2/06 3/06 4/06 1 ford 2 chey 3 dodge OK, on another sheet I have 20,900 lines of items I have sold to these customers. I have the cust name in column A, the date as listed in row 2 is in column E, and the number of pcs sold is in column C. I want to get the total amount sold to each cust during each month. Any help would be great. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Total # Of Cells That Meet Several Criteria | Excel Worksheet Functions | |||
add a column only if 5 other cells on the row satisfy criteria | Excel Worksheet Functions | |||
counting cells (COUNTIF) based on two or more criteria | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
sum cells with criteria | Excel Worksheet Functions |