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
-
Try this.
=IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@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 by marking 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
=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"))))))
-
Hi @PeterR the first error I can notice in your formula is in the first line, the greater than 0 because it will always return "Empty" as everything is greater than 0, instead try using the = instead of greater than only.
Hope this helps,
Cheers,
Ipshita
Ipshita Mukherjee
-
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 by marking 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
=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
-
Hi @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, or are 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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
Sussed, thank you
-
Try this.
=IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@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 by marking 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.8K Get Help
- 438 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 510 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I have a feature column, a task (multiple tasks per feature) column, a task date column and a feature date column. The task dates can be of three different types. They can be all dates. They can be da…
- I am trying to use a formula with CHAR(10) between each missing asset item to build a nice list to use in a record search return automation. I am using one row for each employee with multiple uniform …
- Hello, Everyone. I have a commission sheet with many columns, and I have a 3 part formula to calculate commission based on 3 different % depending on which month of the contract the sales team is in. …
