A dynamic formula to add/multiply cell values of bundles

[filido]

Hi

I would like to know if it's possible to have a formula that says If column C has "&" then add the values of the respective colors from column B to column D.
So basically if I did it manually, it would look something like in column D. It has to dynamic in a way that no matter how many "&" a cell in column C has, the formula works. I don't want to put a million nested IF statements.

To have this even more complicated, I need to stay away from VBA for now. So I'm looking for a formula to write on the formula bar in Excel.

Thanks!

A	B
Blue	2
Yellow	4
Red	6
Green	8
Brown	10

<tbody>
</tbody>

C	D
Blue&Green	formula(=2+8)
Yellow&Green&Red	formula(=4+8+6)
Blue&Yellow&Brown&Red&Green	formula(=2+4+10+6+8)

<tbody>
</tbody>

 

[Peter_SSs]

Is this what you mean?

Excel Workbook
	A	B	C	D
1	Blue	2	Blue&Green	10
2	Yellow	4	Yellow&Green&Red	18
3	Red	6	Blue&Yellow&Brown&Red&Green	30
4	Green	8
5	Brown	10
Add Multiply