I'm using excel 2003 and i need help about the function or formula on my
monthly sale breakdown. I have 3 client table in a same sheet.Where show
date,item,code and weight.I need to count total of weight where all same item
and date in a different table.

Example

Client A
Date Code Item Weight(KG)
1/2 xxxx PA 1000
2/2 xxxx PH 1300
3/2 xxxx PA 2300
4/2 xxxx PA 4300

Client B
Date Code Item Weight(KG)
1/2 xxxx PH 1600
2/2 xxxx PH 1000
5/2 xxxx PA 2800
7/2 xxxx PH 1000

Client C
Date Code Item Weight(KG)
1/2 xxxx PA 4000
2/2 xxxx PH 7300
3/2 xxxx PA 8300
6/2 xxxx PH 4300

Example what i need

Date Item Weight(KG)
1/2 PA ????
2/2 PA ????
3/2 PA ????
4/2 PA ????
--------------------------------
Total ????

I copied you data to a worksheet. I have "Client A" in A1
My dates are in column A; "codes" are in column B; items in C; weights in D
I replaced the first ???? (in C25) with
=SUMPRODUCT(--($A$10:$A$20=A25),--($C$10:$C$20=B25),$D$10:$D$20)
copied this down the column

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html


best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"yguan" wrote in message
...
I'm using excel 2003 and i need help about the function or formula on my
monthly sale breakdown. I have 3 client table in a same sheet.Where show
date,item,code and weight.I need to count total of weight where all same
item
and date in a different table.

Example

Client A
Date Code Item Weight(KG)
1/2 xxxx PA 1000
2/2 xxxx PH 1300
3/2 xxxx PA 2300
4/2 xxxx PA 4300

Client B
Date Code Item Weight(KG)
1/2 xxxx PH 1600
2/2 xxxx PH 1000
5/2 xxxx PA 2800
7/2 xxxx PH 1000

Client C
Date Code Item Weight(KG)
1/2 xxxx PA 4000
2/2 xxxx PH 7300
3/2 xxxx PA 8300
6/2 xxxx PH 4300

Example what i need

Date Item Weight(KG)
1/2 PA ????
2/2 PA ????
3/2 PA ????
4/2 PA ????
--------------------------------
Total ????

Hi

With your data in A1 and down, use this formula in C25 and copy it
down:

=SUMPRODUCT(--($A$3:$A$20=A25),--($C$3:$C$20=B25),$D$3:$D$20)

Use a SUM function to get the Total.

Hopes it helps.

Regards,
Per

On 11 Jan., 20:16, yguan wrote:
I'm using excel 2003 and i need help about the function or formula on my
monthly sale breakdown. I have 3 client table in a same sheet.Where show
date,item,code and weight.I need to count total of weight where all same item
and date in a different table.

Example

Client A
Date * * * Code * * * Item * * * Weight(KG)
1/2 * * * * xxxx * * * * PA * * * * *1000
2/2 * * * * xxxx * * * * PH * * * * *1300
3/2 * * * * xxxx * * * * PA * * * * *2300
4/2 * * * * xxxx * * * * PA * * * * *4300 * *

Client B
Date * * * Code * * * Item * * * Weight(KG)
1/2 * * * * xxxx * * * * PH * * * * *1600
2/2 * * * * xxxx * * * * PH * * * * *1000
5/2 * * * * xxxx * * * * PA * * * * *2800
7/2 * * * * xxxx * * * * PH * * * * *1000

Client C
Date * * * Code * * * Item * * * Weight(KG)
1/2 * * * * xxxx * * * * PA * * * * *4000
2/2 * * * * xxxx * * * * PH * * * * *7300
3/2 * * * * xxxx * * * * PA * * * * *8300
6/2 * * * * xxxx * * * * PH * * * * *4300

Example what i need

Date * * Item * * Weight(KG)
1/2 * * * *PA * * * *????
2/2 * * * *PA * * * *????
3/2 * * * *PA * * * *????
4/2 * * * *PA * * * *????
--------------------------------
Total * * * * * * * * *????

Hi,

If table 1, 2, and 3 are directly above each other then you can use
something like:

=SUMPRODUCT(--(A25=A$3:A$20),--(B25=C$3:C$20),D$3:D$20)

Where A25 is the date and B25 is the Item you want to sum the weight for.

However if they are not situated in this fortuitous layout you will need to
use a longer version:

=SUMPRODUCT(--(A25=A$3:A$6),--(B25=C$3:C$6),D$3:D$6)+SUMPRODUCT(--(A25=$A$10:$A$13),--(B25=$C$10:$C$13),$D$10:$D$13)+SUMPRODUCT(--(A25=$A$17:$A$20),--(B25=$C$17:$C$20),$D$17:$D$20)
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"yguan" wrote:

I'm using excel 2003 and i need help about the function or formula on my
monthly sale breakdown. I have 3 client table in a same sheet.Where show
date,item,code and weight.I need to count total of weight where all same item
and date in a different table.

Example

Client A
Date Code Item Weight(KG)
1/2 xxxx PA 1000
2/2 xxxx PH 1300
3/2 xxxx PA 2300
4/2 xxxx PA 4300

Client B
Date Code Item Weight(KG)
1/2 xxxx PH 1600
2/2 xxxx PH 1000
5/2 xxxx PA 2800
7/2 xxxx PH 1000

Client C
Date Code Item Weight(KG)
1/2 xxxx PA 4000
2/2 xxxx PH 7300
3/2 xxxx PA 8300
6/2 xxxx PH 4300

Example what i need

Date Item Weight(KG)
1/2 PA ????
2/2 PA ????
3/2 PA ????
4/2 PA ????
--------------------------------
Total ????