# 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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### 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
102
Replies
3
Views
184
Replies
0
Views
880
Replies
4
Views
108
Replies
1
Views
359

1,171,317
Messages
5,874,962
Members
433,083
Latest member
ychin130

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