Formula issue showing 0-5 Stars
Can anyone identify the error in this formula - it should show "Three" stars in the column as it's counting 3 true checkbox cells. However it will only return the "Empty" stars result.
Obviously the number of stars formula result is based on how many boxes are checked.
Best Answer
-
Andrée Starå ✭✭✭✭✭✭Try this.
=如果(条件统计([满意地使用超过3倍?]@row:[Insurance Documents Approved]@row, 1) = 5, "Five", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 4, "Four", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 3, "Three", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 2, "Two", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 1, "One", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 0, "Empty"))))))
Did it work?
✅Remember!Did my post(s) help or answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
Answers
-
PeterR ✭✭✭✭
=IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) > 0, "Empty", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) > 1, "One", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) > 2, "Two", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) > 3, "Three", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) > 4, "Four", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) > 5, "Five"))))))
-
Andrée Starå ✭✭✭✭✭✭
Hi@PeterR
I hope you're well and safe!
It should work if you reverse the order.
Did that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
PeterR ✭✭✭✭
=IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) = 0, "Empty", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) = 1, "One", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) = 2, "Two", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) = 3, "Three", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) = 4, "Four", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) = 5, "Five"))))))
I've tried this but it always returns 5 stars, regardless of how many boxes are checked
-
Genevieve P. Employee AdminHi@PeterR
COUNT will simply count how many cells have a box that can be checked, regardless of if the box is checked or not. That means you'll always have 5, as there are 5 cells containing a box.
Instead, you'll need to use COUNTIF to see if the boxes = 1, orare checked:
=IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1)= 0, "Empty", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1)= 1, "One", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 2, "Two", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1)= 3, "Three", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1)= 4, "Four", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1)= 5, "Five"))))))
Cheers!
Genevieve
-
PeterR ✭✭✭✭
Sussed, thank you
-
Andrée Starå ✭✭✭✭✭✭
Try this.
=如果(条件统计([满意地使用超过3倍?]@row:[Insurance Documents Approved]@row, 1) = 5, "Five", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 4, "Four", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 3, "Three", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 2, "Two", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 1, "One", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 0, "Empty"))))))
Did it work?
✅Remember!Did my post(s) help or answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/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":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+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-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+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-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
