software

A B C D E F
1/06 2/06 3/06 4/06
1 ford
2 chey
3 dodgeOK, 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.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
peltiertech.com/Excel/Pivots/pivotstart.htm
and
www.contextures.com/xlPivot02.html

--
Regards

Roger Govierquot;Cube Farmerquot; gt; wrote in message
...
gt;A B C D E F
gt; 1/06 2/06 3/06 4/06
gt; 1 ford
gt; 2 chey
gt; 3 dodge
gt;
gt;
gt; OK, on another sheet I have 20,900 lines of items I have sold to these
gt; customers.
gt; I have the cust name in column A, the date as listed in row 2 is in
gt; column
gt; E, and the number of pcs sold is in column C. I want to get the total
gt; amount
gt; sold to each cust during each month. Any help would be great.
gt;
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 quot;Sheetquot; without a #.
Any further options?
Thanks,
Mike
quot;Roger Govierquot; wrote:

gt; Hi
gt;
gt; One way, in cell B2 of Sheet2 enter
gt; =SUMPRODUCT(--(Sheet1!$A$2:$A$20900)=A2),--(Sheet!$E$2:$E$20900=B$1),$C2:$C20900)
gt; Copy across for the required number of columns.
gt; Copy the set of formulae down for the range of Values in column A.
gt;
gt; Better still would be to use a Pivot table on your data on Sheet1.
gt; For help on setting up Pivot tables take a look at
gt; peltiertech.com/Excel/Pivots/pivotstart.htm
gt; and
gt; www.contextures.com/xlPivot02.html
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; quot;Cube Farmerquot; gt; wrote in message
gt; ...
gt; gt;A B C D E F
gt; gt; 1/06 2/06 3/06 4/06
gt; gt; 1 ford
gt; gt; 2 chey
gt; gt; 3 dodge
gt; gt;
gt; gt;
gt; gt; OK, on another sheet I have 20,900 lines of items I have sold to these
gt; gt; customers.
gt; gt; I have the cust name in column A, the date as listed in row 2 is in
gt; gt; column
gt; gt; E, and the number of pcs sold is in column C. I want to get the total
gt; gt; amount
gt; gt; sold to each cust during each month. Any help would be great.
gt; gt;
gt;
gt;
gt;

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

quot;Cube Farmerquot; gt; wrote in message
...
gt; Roger,
gt; Thanks for the attempt. I couldn't get it to work though( i'm sure it's my
gt; doing)
gt; =SUMPRODUCT(--(Sheet3!$A$2:$A$20903)=A5)--(Sheet3!$G$2:$G$20903=$B$4)*(Sheet3!$E2:$E20903)
gt; That is what it ended up as when MS put in its fix. I changed some of the
gt; row and column #s to fit actual. On your formula the last part didn't call
gt; out another sheet. Is this correct? Also on the middle section it called
gt; out
gt; only quot;Sheetquot; without a #.
gt; Any further options?
gt; Thanks,
gt; Mike
gt;
gt;
gt;
gt; quot;Roger Govierquot; wrote:
gt;
gt;gt; Hi
gt;gt;
gt;gt; One way, in cell B2 of Sheet2 enter
gt;gt; =SUMPRODUCT(--(Sheet1!$A$2:$A$20900)=A2),--(Sheet!$E$2:$E$20900=B$1),$C2:$C20900)
gt;gt; Copy across for the required number of columns.
gt;gt; Copy the set of formulae down for the range of Values in column A.
gt;gt;
gt;gt; Better still would be to use a Pivot table on your data on Sheet1.
gt;gt; For help on setting up Pivot tables take a look at
gt;gt; peltiertech.com/Excel/Pivots/pivotstart.htm
gt;gt; and
gt;gt; www.contextures.com/xlPivot02.html
gt;gt;
gt;gt; --
gt;gt; Regards
gt;gt;
gt;gt; Roger Govier
gt;gt;
gt;gt;
gt;gt; quot;Cube Farmerquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;A B C D E F
gt;gt; gt; 1/06 2/06 3/06 4/06
gt;gt; gt; 1 ford
gt;gt; gt; 2 chey
gt;gt; gt; 3 dodge
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; OK, on another sheet I have 20,900 lines of items I have sold to these
gt;gt; gt; customers.
gt;gt; gt; I have the cust name in column A, the date as listed in row 2 is in
gt;gt; gt; column
gt;gt; gt; E, and the number of pcs sold is in column C. I want to get the total
gt;gt; gt; amount
gt;gt; gt; sold to each cust during each month. Any help would be great.
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;Thanks Peo,
That did the trick!!
Mikequot;Peo Sjoblomquot; wrote:

gt; Try this, DON'T let excel try to change it
gt;
gt; =SUMPRODUCT(--(Sheet3!$A$2:$A$20903=A5),--(Sheet3!$G$2:$G$20903=$B$4),Sheet3!$E2:$E20903)
gt;
gt; will lookup A5 in the A range where the G range is equal to B4 then sum
gt; those conditions in E
gt;
gt; --
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Portland, Oregon
gt;
gt;
gt;
gt;
gt; quot;Cube Farmerquot; gt; wrote in message
gt; ...
gt; gt; Roger,
gt; gt; Thanks for the attempt. I couldn't get it to work though( i'm sure it's my
gt; gt; doing)
gt; gt; =SUMPRODUCT(--(Sheet3!$A$2:$A$20903)=A5)--(Sheet3!$G$2:$G$20903=$B$4)*(Sheet3!$E2:$E20903)
gt; gt; That is what it ended up as when MS put in its fix. I changed some of the
gt; gt; row and column #s to fit actual. On your formula the last part didn't call
gt; gt; out another sheet. Is this correct? Also on the middle section it called
gt; gt; out
gt; gt; only quot;Sheetquot; without a #.
gt; gt; Any further options?
gt; gt; Thanks,
gt; gt; Mike
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Roger Govierquot; wrote:
gt; gt;
gt; gt;gt; Hi
gt; gt;gt;
gt; gt;gt; One way, in cell B2 of Sheet2 enter
gt; gt;gt; =SUMPRODUCT(--(Sheet1!$A$2:$A$20900)=A2),--(Sheet!$E$2:$E$20900=B$1),$C2:$C20900)
gt; gt;gt; Copy across for the required number of columns.
gt; gt;gt; Copy the set of formulae down for the range of Values in column A.
gt; gt;gt;
gt; gt;gt; Better still would be to use a Pivot table on your data on Sheet1.
gt; gt;gt; For help on setting up Pivot tables take a look at
gt; gt;gt; peltiertech.com/Excel/Pivots/pivotstart.htm
gt; gt;gt; and
gt; gt;gt; www.contextures.com/xlPivot02.html
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; Regards
gt; gt;gt;
gt; gt;gt; Roger Govier
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;Cube Farmerquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt;A B C D E F
gt; gt;gt; gt; 1/06 2/06 3/06 4/06
gt; gt;gt; gt; 1 ford
gt; gt;gt; gt; 2 chey
gt; gt;gt; gt; 3 dodge
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt; gt; OK, on another sheet I have 20,900 lines of items I have sold to these
gt; gt;gt; gt; customers.
gt; gt;gt; gt; I have the cust name in column A, the date as listed in row 2 is in
gt; gt;gt; gt; column
gt; gt;gt; gt; E, and the number of pcs sold is in column C. I want to get the total
gt; gt;gt; gt; amount
gt; gt;gt; gt; sold to each cust during each month. Any help would be great.
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;