Trying to find the percentage of time that a word appears in a column.
I have a column with Red, Green and Yellow but I want to find the percentage of times the word Red is used.
I tried =COUNTIF([All screeners present at event]:[All screeners present at event], "Red"), /COUNT([All screeners present at event]:[All screeners present at event])) but I'm getting an error
Answers
-
ShelbyWarren ✭✭✭✭✭
@Connie CochranTry this:
=(COUNTIF([All screeners present at event]:[All screeners present at event], "Red")) / COUNT([All screeners present at event]:[All screeners present at event])
-
Thank you but I am still getting an error saying #UNPARSEBLE
-
ShelbyWarren ✭✭✭✭✭
@Connie CochranStrange. It's working for me in the example below. Can you share a screenshot of the sheet/columns?
-
If figured it out, I had space that I shouldn't have. Thank you so much for your help!!!!
-
ShelbyWarren ✭✭✭✭✭
@Connie CochranGlad it worked out!
-
What if instead of the word red I just wanted to find the percentage of all cells that had a value in it some cells are blank. Like there are 50 cells but only 5 of them have anything in it =10%
-
ShelbyWarren ✭✭✭✭✭
@Connie CochranDoes the primary column of the sheet always have a value in it? Or is there another column that always has a value in it? If so, use this formula but replace the "Primary Column" with a column that always will have a value:
=(COUNT([All screeners present at event]:[All screeners present at event]) / (COUNT([Primary Column]:[Primary Column])))
-
AMAZING! Thank you so much!
Help Article Resources
Categories
The formula for this would be:<\/p>
=SUMIF(Project:Project, \"A\", [Grand Total]:[Grand Total])<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/E9ZBLZJJ9UO9\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\nHope this helps - any issues etc. then just post! 😊<\/span><\/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":106990,"type":"question","name":"JOIN COLLECT for Checkboxes","excerpt":"Hello All, Is there a way to collect information using a JOIN COLLECT formula? Column 2 is Name Column 3 thru 5 ASL. Columns 6 thru 8 is are the names I want to populate based on the checkboxes (ASL). Column 3 can have two checkboxes versus 1. Ultimately I would like all checkboxes that are checked to populate names in…","snippet":"Hello All, Is there a way to collect information using a JOIN COLLECT formula? Column 2 is Name Column 3 thru 5 ASL. Columns 6 thru 8 is are the names I want to populate based on…","categoryID":322,"dateInserted":"2023-06-28T02:32:36+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T12:51:39+00:00","insertUserID":162875,"insertUser":{"userID":162875,"name":"hello1030","url":"https:\/\/community.smartsheet.com\/profile\/hello1030","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nH14DQG3YON84.png","dateLastActive":"2023-06-28T13:09:04+00:00","banned":0,"punished":0,"private":true,"label":"✭"},"updateUserID":null,"lastUserID":162875,"lastUser":{"userID":162875,"name":"hello1030","url":"https:\/\/community.smartsheet.com\/profile\/hello1030","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nH14DQG3YON84.png","dateLastActive":"2023-06-28T13:09:04+00:00","banned":0,"punished":0,"private":true,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":21,"score":null,"hot":3375878655,"url":"https:\/\/community.smartsheet.com\/discussion\/106990\/join-collect-for-checkboxes","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106990\/join-collect-for-checkboxes","format":"Rich","lastPost":{"discussionID":106990,"commentID":382809,"name":"Re: JOIN COLLECT for Checkboxes","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382809#Comment_382809","dateInserted":"2023-06-28T12:51:39+00:00","insertUserID":162875,"insertUser":{"userID":162875,"name":"hello1030","url":"https:\/\/community.smartsheet.com\/profile\/hello1030","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nH14DQG3YON84.png","dateLastActive":"2023-06-28T13:09:04+00:00","banned":0,"punished":0,"private":true,"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-28T12:51:52+00:00","dateAnswered":"2023-06-28T08:50:42+00:00","acceptedAnswers":[{"commentID":382755,"body":" Hi @hello1030<\/a> <\/p> I hope you're well and safe!<\/p> Try something like this. (I've guessed what your formula would look like, but you'd have to update the column names to match yours)<\/p> =JOIN(COLLECT([COLUMN2]:[COLUMN2], [COLUMN2]:[COLUMN2], 1, [COLUMN3]:[COLUMN3], \"Support\"), CHAR(10))<\/p> Did that work\/help? <\/p> There were a lot of errors in the formula, but it was mainly the brackets { is used for cross-sheet formulas, and you have to close the [ ] square brackets around a column name that doesn't only have one piece of text, and then the structure wasn't correct.<\/em><\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":106973,"type":"question","name":"SUMIFS with CONTAINS","excerpt":"Hello, I want to write a formula that SUMS charges (\"Ext Price\" column) from all item descriptions that contain a word (\"CF\") from an accounting \"Activity Description\" column. I've tried various versions of this. =SUMIFS(CONTAINS({Activity Description}, \"CF\", {Ext Price}) =SUMIFS({Activity Description}, CONTAINS(CF), {Ext…","snippet":"Hello, I want to write a formula that SUMS charges (\"Ext Price\" column) from all item descriptions that contain a word (\"CF\") from an accounting \"Activity Description\" column.…","categoryID":322,"dateInserted":"2023-06-27T19:19:06+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T12:30:22+00:00","insertUserID":162846,"insertUser":{"userID":162846,"name":"UserAHC","title":"Senior Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/UserAHC","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3bF0P9bUFHs!!W5i-ZnFYhF2","dateLastActive":"2023-06-27T21:25:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-28T13:21:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":27,"score":null,"hot":3375850768,"url":"https:\/\/community.smartsheet.com\/discussion\/106973\/sumifs-with-contains","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106973\/sumifs-with-contains","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106973,"commentID":382803,"name":"Re: SUMIFS with CONTAINS","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382803#Comment_382803","dateInserted":"2023-06-28T12:30:22+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-28T13:21:59+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-28T09:44:38+00:00","dateAnswered":"2023-06-27T19:47:23+00:00","acceptedAnswers":[{"commentID":382674,"body":" Try this:<\/p> =SUMIFS({Ext Price}, {Activity Description}, CONTAINS(\"CF\", @cell))<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
