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!
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Brown[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]C[/TD]
[TD="align: center"]D [/TD]
[/TR]
[TR]
[TD]Blue&Green[/TD]
[TD]formula(=2+8)[/TD]
[/TR]
[TR]
[TD]Yellow&Green&Red[/TD]
[TD]formula(=4+8+6)[/TD]
[/TR]
[TR]
[TD]Blue&Yellow&Brown&Red&Green[/TD]
[TD]formula(=2+4+10+6+8)[/TD]
[/TR]
</tbody>[/TABLE]
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!
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Brown[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]C[/TD]
[TD="align: center"]D [/TD]
[/TR]
[TR]
[TD]Blue&Green[/TD]
[TD]formula(=2+8)[/TD]
[/TR]
[TR]
[TD]Yellow&Green&Red[/TD]
[TD]formula(=4+8+6)[/TD]
[/TR]
[TR]
[TD]Blue&Yellow&Brown&Red&Green[/TD]
[TD]formula(=2+4+10+6+8)[/TD]
[/TR]
</tbody>[/TABLE]