How can I sum cells that include the abbreviation of states, but include commas in between?
As of right now I am summing selected cells, which then gives me everything that is in those cells. It is then merging them together, but I need it to include a comma if there is more than 1 cell that has information in it.
Best Answer
-
Paul Newcome ✭✭✭✭✭✭Try this instead:
=JOIN(COLLECT([Dash Columns]12:[Dash Columns]61, [Dash Columns]12:[Dash Columns]61, @cell <> ""), ", ")
thinkspi.com
Answers
-
Eric Law ✭✭✭✭✭@haley brianneYour screenshot is not really showing a lot. Can you recapture the screen shot and also give an example of what you want?
-
应该有一个逗号之间我和德。are abbreviations of states.@Eric Law
-
Paul Newcome ✭✭✭✭✭✭
Can you provide the entire formula you are using?
thinkspi.com
-
=[Dash Columns]61 + [Dash Columns]60 + [Dash Columns]59 + [Dash Columns]58 + [Dash Columns]57 + [Dash Columns]56 + [Dash Columns]55 + [Dash Columns]54 + [Dash Columns]53 + [Dash Columns]52 + [Dash Columns]51 + [Dash Columns]50 + [Dash Columns]49 + [Dash Columns]48 + [Dash Columns]47 + [Dash Columns]46 + [Dash Columns]45 + [Dash Columns]44 + [Dash Columns]43 + [Dash Columns]42 + [Dash Columns]41 + [Dash Columns]40 + [Dash Columns]39 + [Dash Columns]37 + [Dash Columns]38 + [Dash Columns]36 + [Dash Columns]35 + [Dash Columns]34 + [Dash Columns]33 + [Dash Columns]32 + [Dash Columns]31 + [Dash Columns]30 + [Dash Columns]29 + [Dash Columns]28 + [Dash Columns]27 + [Dash Columns]26 + [Dash Columns]25 + [Dash Columns]23 + [Dash Columns]24 + [Dash Columns]22 + [Dash Columns]21 + [Dash Columns]20 + [Dash Columns]19 + [Dash Columns]18 + [Dash Columns]17 + [Dash Columns]16 + [Dash Columns]15 + [Dash Columns]14 + [Dash Columns]13 + [Dash Columns]12@Paul Newcome
-
Paul Newcome ✭✭✭✭✭✭
Try this instead:
=JOIN(COLLECT([Dash Columns]12:[Dash Columns]61, [Dash Columns]12:[Dash Columns]61, @cell <> ""), ", ")
thinkspi.com
-
Perfect, thank you@Paul Newcome!
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
Check out theFormula Handbook template!
=IF(COUNTIF(CHILDREN([BallCell]@row), \"Empty\") = COUNT(CHILDREN([BallCell]@row)), \"Not Started\", IF(COUNTIF(CHILDREN([BallCell]@row), \"Full\") = COUNT(CHILDREN([BallCell]@row)), \"Completed\", \"In Progress\"))<\/p>
If you do have your sheet setup somehow so that the entries are respresented by [0, 0.25, 0.50, 0.75, 1], then this should work:<\/p>
=IF(COUNTIF(CHILDREN([BallCell]@row), < 0.25) = COUNT(CHILDREN([BallCell]@row)), \"Not Started\", IF(COUNTIF(CHILDREN([BallCell]@row), 1) = COUNT(CHILDREN([BallCell]@row)), \"Completed\", \"In Progress\"))<\/p>
In either case, you will need to substitute [BallCell] with the name of the column with your symbols. The column name is not visible in your screenshot.<\/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":106869,"type":"question","name":"Formula to Assign Symbol based on a cell's value","excerpt":"This is a complicated one that I once got to work with RGY balls, but cannot figure out how to apply this to the Pain scale emojis. What I am trying to do: (Using the pain scale symbols) Have symbols automatically changed based on the value in the corresponding cell. I am already using a formula to assign a value based on…","categoryID":322,"dateInserted":"2023-06-24T20:02:20+00:00","dateUpdated":null,"dateLastComment":"2023-06-25T22:29:54+00:00","insertUserID":158092,"insertUser":{"userID":158092,"name":"MeganJF","url":"https:\/\/community.smartsheet.com\/profile\/MeganJF","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DgFow9e-1vo!YfMmmJOUTjo!UDpVBcB6ikY","dateLastActive":"2023-06-25T17:43:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":139601,"lastUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-06-26T02:59:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":27,"score":null,"hot":3375372134,"url":"https:\/\/community.smartsheet.com\/discussion\/106869\/formula-to-assign-symbol-based-on-a-cells-value","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106869\/formula-to-assign-symbol-based-on-a-cells-value","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106869,"commentID":382193,"name":"Re: Formula to Assign Symbol based on a cell's value","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382193#Comment_382193","dateInserted":"2023-06-25T22:29:54+00:00","insertUserID":139601,"insertUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-06-26T02:59:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-25T17:10:13+00:00","dateAnswered":"2023-06-25T03:32:30+00:00","acceptedAnswers":[{"commentID":382165,"body":"
Hi @MeganJF<\/p>
I guess you forget to use AND( ).<\/p>
For example, <\/p>
- IF([Total Value to User Score]@row = <3.8, >4.7, \"Mild,<\/li><\/ul>
should be<\/p>
- IF(AND(<\/strong>[Total Value to User Score]@row < 3.8, [Total Value to User Score]@row > 4.7)<\/strong>, \"Mild\",<\/li><\/ul>
However, the following would be more straightforward.<\/p>
- =IF([Total Value to User Score]@row >= 4.8, \"No Pain\", <\/li>
- IF([Total Value to User Score]@row >= 3.8, \"Mild\", <\/li>
- IF([Total Value to User Score]@row >= 2.8, \"Moderate\", <\/li>
- IF([Total Value to User Score]@row >= 1.8, \"Very Severe\", <\/li>
- IF([Total Value to User Score]@row < 1.8, \"Extreme\", \"//www.santa-greenland.com/community/discussion/106637/\")))))<\/li><\/ul>
- IF(AND(<\/strong>[Total Value to User Score]@row < 3.8, [Total Value to User Score]@row > 4.7)<\/strong>, \"Mild\",<\/li><\/ul>
![\"Formula](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/N18M5WZN6TYQ\/formula-to-assign-symbol-based-on-a-cell-27s-value.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
