Formula - how to?

[Camel123]

Hi,

In sheet 1 I have product ID's that might be stacked with multiple ID's in a string. Product ID's might have different amount of numbers and combinations with letters, e.g.:

Cell/product ID
A1: 12345, A1122334, 7654321
A2: A9988776, 54321
A3: 7776655

In sheet 2 I have all invoices per product ID (might be several invoices per product).

B: product ID, C: cost

B1: 12345, C1: 100$
B2: A1122334, C2: 50$
B3: A1122334, C3: 200$

For instance, A1122334 occurs in A1 on sheet 1, then I want the formula to sum all values for this product ID from sheet 2= 50+200= 250$.

I would really appreciate your input on this issue.

 

[DanteAmor]

Hi,

In sheet 1 I have product ID's that might be stacked with multiple ID's in a string. Product ID's might have different amount of numbers and combinations with letters, e.g.:

Cell/product ID
A1: 12345, A1122334, 7654321
A2: A9988776, 54321
A3: 7776655

In sheet 2 I have all invoices per product ID (might be several invoices per product).

B: product ID, C: cost

B1: 12345, C1: 100$
B2: A1122334, C2: 50$
B3: A1122334, C3: 200$

For instance, A1122334 occurs in A1 on sheet 1, then I want the formula to sum all values for this product ID from sheet 2= 50+200= 250$.

I would really appreciate your input on this issue.

In your example ID 12345 is also in cell A1, so the result should be 350?
Where do you want the result on sheet1 in cell B1?

Or better, you could put the expected result for each row and on which sheet.