Sum of multi select dropdown items with assigned values
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
Answers
-
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
-
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.
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!