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
-
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
-
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.
你可能知道,如果你想使用new column formula functionality, you would replace your numeric row number designation(eg 25) with@row
cheers,
Kelly
Help Article Resources
Categories
=IF(AND([Average Score]@row>= 5, [Average Score]@row<= 9), \"Project\", IF(AND([Average Score]@row>= 10, [Average Score]@row<= 15), \"Program\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":110985,"type":"question","name":"Dashboard Last Updated Date","excerpt":"I would like to display the last time the dashboard was updated. My approach was adding a date field in the summary data that automatically pulls the last saved date of the sheet. I don't think I can do a formula in the date field type. Any other suggestions?","snippet":"I would like to display the last time the dashboard was updated. My approach was adding a date field in the summary data that automatically pulls the last saved date of the sheet.…","categoryID":322,"dateInserted":"2023-09-29T12:56:38+00:00","dateUpdated":"2023-09-29T23:58:27+00:00","dateLastComment":"2023-09-29T16:19:19+00:00","insertUserID":167704,"insertUser":{"userID":167704,"name":"Jenn Moffett","title":"Director, PMO","url":"https:\/\/community.smartsheet.com\/profile\/Jenn%20Moffett","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-29T15:38:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":166028,"lastUserID":167704,"lastUser":{"userID":167704,"name":"Jenn Moffett","title":"Director, PMO","url":"https:\/\/community.smartsheet.com\/profile\/Jenn%20Moffett","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-29T15:38:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":35,"score":null,"hot":3391997757,"url":"https:\/\/community.smartsheet.com\/discussion\/110985\/dashboard-last-updated-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110985\/dashboard-last-updated-date","format":"Rich","tagIDs":[292,335],"lastPost":{"discussionID":110985,"commentID":397701,"name":"Re: Dashboard Last Updated Date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397701#Comment_397701","dateInserted":"2023-09-29T16:19:19+00:00","insertUserID":167704,"insertUser":{"userID":167704,"name":"Jenn Moffett","title":"Director, PMO","url":"https:\/\/community.smartsheet.com\/profile\/Jenn%20Moffett","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-29T15:38:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-09-29T15:39:37+00:00","dateAnswered":"2023-09-29T14:10:25+00:00","acceptedAnswers":[{"commentID":397662,"body":"
