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.

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.

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.

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.

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.