Help! Sum value by refer another column

andy5323

New Member
Joined
Jan 14, 2004
Messages
16
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
ABCDE
1ProductReqJanFeb
2aaareque11
3aaaT321
4aaaT41
5bbbreque01
6bbbT300
7cccreque11
8cccT301
9cccT411
10
Sheet1


The output that i need.
Book.xls
ABCDE
1ProductReqJanFeb
2aaareque11
3aaaT3+T422
4bbbreque01
5bbbT300
6cccreque11
7cccT3+T412
8
Sheet2



Have anyone can provide the solution? Thanks a lots

Regards,

Andy
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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

New Member
Joined
Jan 14, 2004
Messages
16
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
just_jon said:
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! :LOL:
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

Better, Brian; however VBA is desired...
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Given the way the conditions are represented in the destination, something like this would be required...
andy5323.xls
ABCDE
1ProductReqJanFeb
2aaareque11
3aaaT3+T422
4bbbreque01
5bbbT300
6cccreque11
7cccT3+T412
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

New Member
Joined
Jan 14, 2004
Messages
16
Thanks

Hi Experts,

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


Regards,
Andy (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,704
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top