Help! Sum value by refer another column

[andy5323]

Hi expert:

Have a problem need help from you all. I need to sumvalue of T3+ T4 on January, February that i show at sheet1 of the worksheet. And i need to display the output that show as like sheet2. When the data field [product] is same, it will sumvalue of the T3 and T4.

[remark: 1. some of product don't have T3 or T4.
2. The value of January and February maybe is empty.]

Book.xls
	A	B	C	D	E
1	Product	Req	Jan	Feb
2	aaa	reque	1	1
3	aaa	T3	2	1
4	aaa	T4		1
5	bbb	reque	0	1
6	bbb	T3	0	0
7	ccc	reque	1	1
8	ccc	T3	0	1
9	ccc	T4	1	1
10
Sheet1

The output that i need.

Book.xls
	A	B	C	D	E
1	Product	Req	Jan	Feb
2	aaa	reque	1	1
3	aaa	T3+T4	2	2
4	bbb	reque	0	1
5	bbb	T3	0	0
6	ccc	reque	1	1
7	ccc	T3+T4	1	2
8
Sheet2

Have anyone can provide the solution? Thanks a lots

Regards,

Andy

 

[just_jon]

Try, in Sheet2 C3 --

=SUMPRODUCT(--(Sheet1!$A$1:$A$9=$A3),--(Sheet1!$B$1:$B$9={"T3","T4"}),(Sheet1!C$1:C$9))

and copy to D3, then down.

 

[andy5323]

Hi just_jon,

Thanks you for so fast reply me. But how to i transfer this into the macro VBA code, like excute in the module?

Regards,
Andy

 

[Brian from Maui]

Try, in Sheet2 C3 --

=SUMPRODUCT(--(Sheet1!$A$1:$A$9=$A3),--(Sheet1!$B$1:$B$9={"T3","T4"}),(Sheet1!C$1:C$9))

and copy to D3, then down.

=SUMPRODUCT(--(sheet1!$A$1:$A$9=$A3),--ISNUMBER(MATCH(sheet1!$B$1:$B$9,$F$1:$G$1,0)),sheet1!$C$1:$C$9)

where F1 and G1 houses T3 and T4

couldn't resist!

 

[just_jon]

Better, Brian; however VBA is desired...

 

[Brian from Maui]

Better, Brian; however VBA is desired...

I know......

 

[Aladin Akyurek]

Given the way the conditions are represented in the destination, something like this would be required...

andy5323.xls
	A	B	C	D	E
1	Product	Req	Jan	Feb
2	aaa	reque	1	1
3	aaa	T3+T4	2	2
4	bbb	reque	0	1
5	bbb	T3	0	0
6	ccc	reque	1	1
7	ccc	T3+T4	1	2
8
9
Sheet2

The formula in C1, which is copied across then down, is:

=SUMPRODUCT(--(Sheet1!$A$2:$A$9=$A2),--(ISNUMBER(MATCH(Sheet1!$B$2:$B$9,EVAL("{"&CHAR(34)&MCONCAT(SUBSTITUTE($B2,"+",CHAR(34)&","&CHAR(34)))&CHAR(34)&"}"),0))),INDEX(Sheet1!$C$2:$D$9,0,MATCH(C$1,Sheet1!$C$1:$D$1,0)))

Morefunc.xll add-in is of course required.

 

[andy5323]

Thanks

Hi Experts,

Thanks again for Aladin Akyurek, just_jon and Brian. Thanks for your all help.


Regards,
Andy