A dynamic formula to add/multiply cell values of bundles

filido

New Member
Joined
Jun 7, 2019
Messages
21
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]
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Is this what you mean?

Excel Workbook
ABCD
1Blue2Blue&Green10
2Yellow4Yellow&Green&Red18
3Red6Blue&Yellow&Brown&Red&Green30
4Green8
5Brown10
Add Multiply
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top