# Help! Sum value by refer another column

#### andy5323

##### New Member
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

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### just_jon

##### Legend
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
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
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!

#### just_jon

##### Legend
Better, Brian; however VBA is desired...

#### Brian from Maui

##### MrExcel MVP
just_jon said:
Better, Brian; however VBA is desired...

I know......

##### MrExcel MVP
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
Thanks

Hi Experts,

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

Regards,
Andy

Replies
3
Views
104
Replies
3
Views
187
Replies
0
Views
887
Replies
4
Views
109
Replies
1
Views
364

1,171,538
Messages
5,876,094
Members
433,175
Latest member
hp109

### 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.

### Which adblocker are you using?

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

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