Assigning and summing values from Dropdown column labels

Platform discussions
,
1

I am trying to create a formula column where I assign values to dropdown labels, and sum them if they are included. I’ve tried an if formula, but if I add multiple labels, it doesn’t seem to work.

Basically, I have a column with multiple options (a few examples are below), which each have a different monetary value (added in brackets).

{Interventions}: “Paving”(£5000), “Kerb” (£6000), “Crossing” (£12000), “Lights” (£2000).

I want to create a formula that assigns their value, and then adds all labels values together. Some rows may have paving & kerb, whereas another may have just crossing, and one may have kerb, lights and a crossing. If none are used, the value can be 0.

Any help much appreciated!

2

Hey @ELBLS - rather than a formula column, why not split all the options into a separate board with their associated costs. Then use a Connect Board column to this new board and mirror the costs column from the new board.

This way as costs change or services are added, its making simple adjustments to the board rather than adjusting formulas.

image
image821×627 31.5 KB

Additionally, if you make it a 2-way connection, you can then view the jobs utilizing the chosen services from the new service board:

image
image741×472 27.6 KB

Hope this helps!
Mark

3

Hi Mark! This is great- and I really appreciate it- but I use tags a lot, and don’t want to overload on boards. I will defo put this in for interim, but I’d love a formula solution if anyone has one too, as it’s the kind of thing that I would use a lot :smiley:

4

Hey @ELBLS - here is a formula you can use:

IF(SEARCH("Paving",{Dropdown})>0,5000,0) +
IF(SEARCH("Kerb",{Dropdown})>0,6000,0) +
IF(SEARCH("Crossing",{Dropdown})>0,12000,0) +
IF(SEARCH("Lights",{Dropdown})>0,2000,0)

image

Thanks,
Mark

2 Likes
5

Thank you so much!

1 Like