Issue finding a formula that works on a cell with multiple values (codes)

[MPFraser7]

Codes Formula
A001,A002 (return 10+20+30=60)
B001 (return 40=40)

[table="width: 500"]
[tr]
[td]Code Data1 Data2
A001 10 20
A002 30
B001 40[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[/table]


I have two tables, one with the Codes and Formula columns, the second with the Code, Data1 and Data2 column. In the codes column, the codes are entered in one cell and are comma separated. I'm trying to enter a formula that selects the comma separated codes and returns Data1 and Data2 values from the second table when the codes match. It would be easy to do if I was to split the Codes column into two or more, but I have to keep multiple codes per cell. I'm writing in my second language so please let me know if I should explain it better.

 

[MPFraser7]

My table didn't work as intended.

Codes: row 1 = A001, A003 row 2 = B001
Formula: row 1 = (return 10+20+30=60) row 2 = (return 40=40)

Code: Row 1 = A001 Row 2 = A002 Row 3 = B001
Data1: Row 1 = 10 Row 2 = 30 Row 3 =0
Data2: Row 1 = 20 Row 2 = 0 Row 3 = 40

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td]Codes[/td][td]Formula[/td][td][/td][/tr]
[tr][td]

2​

[/td][td]A001,A002[/td][td]

60​

[/td][td][/td][/tr]
[tr][td]

3​

[/td][td]B001[/td][td]

40​

[/td][td][/td][/tr]
[tr][td]

4​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]Code[/td][td]Data1[/td][td]Data2[/td][/tr]
[tr][td]

6​

[/td][td]A001[/td][td]

10​

[/td][td]

20​

[/td][/tr]
[tr][td]

7​

[/td][td]A002[/td][td]

30​

[/td][td][/td][/tr]
[tr][td]

8​

[/td][td]B001[/td][td]

40​

[/td][td][/td][/tr]
[/table]

In B2 control+shift+enter, not just enter, and copy down:

=SUM(IF(ISNUMBER(SEARCH($A$6:$A$8&",",$A2&",")),$B$6:$C$8))

 

[MPFraser7]

If A8 is blank, the formula in B2 returns 100. I want to modify it so that it would still return 60, can you please instruct me on how to do so? Other than that, the formula works very well and will be put to good use. Thanks!

 

[Aladin Akyurek]

If A8 is blank, the formula in B2 returns 100. I want to modify it so that it would still return 60, can you please instruct me on how to do so? Other than that, the formula works very well and will be put to good use. Thanks!

Right. In B2 control+shift+enter and copy down:

=SUM(IF(ISNUMBER(SEARCH(IF($A$6:$A$8="","#",$A$6:$A$8)&",",$A2&",")),$B$6:$C$8))