# 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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### 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
0
Views
872
Replies
1
Views
356
Replies
1
Views
309
Replies
4
Views
397
Replies
3
Views
1K

1,171,066
Messages
5,873,588
Members
432,985
Latest member
leahw

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