Sum of multi select dropdown items with assigned values

Steven Binder

I have a column (Design Software) formatted as Dropdown (multi select). The dropdown values are names of software products. I want to give a value to each software reflecting the respective cost (e.g., Adobe Substance = 3000, Browzwear vStitcher = 19000, etc.). I added the formula below in another column (Software Cost) to assign a cost value to each software.

=IF([Design Software]@row = "Adobe Substance", 3000, IF([Design Software]@row = "Browzwear vStitcher", 19000, IF([Design Software]@row = "CLO", 19800)))

I would like to add another column (Total Software Cost) that adds (SUM) the respective cost of all the software selected in the multi select dropdown from Design Software. What is the best way to go about this? Thank you in advance for any advice.

Best,

Steven

Kelly Moore

Hi Steven,

One solution could be to utilize the Sheet summary formulas (found in the right-hand panel of the sheet window). I would build a countif formula for each product as a sheet summary formula then add them all together (also as a sheet summary formula). If needed, the data could be pulled into a sheet summary report.

Below is the formula. I always use CountifS (plural) instead of the usual Countif(singular) since countifS always work, even when only one criteria is needed in the equation. The HAS function was created specifically for multi-select columns.

=COUNTIFS([Design Software]:[Design Software], HAS(@cell, "Product A")) * 3000

Below is how it looks in the sheet summary window. You would substitute your product name in for "Product A". Once all the individual formulas are built by substituting in each product name (note the cost is already built into the formulas), build a total Sum formula to sum all into the grand total.

If you didn't want to use the sheet summary formulas, you'll need to put these in individual cells, or combine into one big formula

=(COUNTIFS([Design Software]:[Design Software], HAS(@cell, "Product A")) * 3000)+(COUNTIFS([Design Software]:[Design Software], HAS(@cell, "Product B")) * 19000)+etc

Kelly

Steven Binder

Thank you, Kelly. This was helpful. Since I was trying to calculate the total cost for all software selected in the Design Software field for each row, rather than calculating the total cost for the Design Software column, I used your combined formula and added the row number in each Total Software Cost field. I hope that makes sense.

=(COUNTIF([Design Software]25, HAS(@cell, "Browzwear vStitcher")) * 19000) + (COUNTIF([Design Software]25, HAS(@cell, "CLO")) * 19800)

25 = row number.

Kelly Moore

Great! I missed the part that you needed it per row. Glad you fixed it.

As you're probably aware, if you wanted to use the new column formula functionality, you would replace your numeric row number designation(eg 25) with @row

cheers,

Kelly