Adding IFERROR Formula
Smartsheet Community:
I am Trying to Add IFERROR to The Below Formula, but Receving an Error.
Current Formula:
=ROUND((COUNTIFS(CHILDREN(), OR(@cell = "Pass", @cell = "Fail-4Front Review", @cell = "Fail-Shorr Review", @cell = "Re-Test Ready")) / COUNTIFS(CHILDREN(), @cell <> "")) * 100) + "%"
Modified Formula with UPARSEABLE Error:
=IFERROR(ROUND((COUNTIFS(CHILDREN(), OR(@cell = "Pass", @cell = "Fail-4Front Review", @cell = "Fail-Shorr Review", @cell = "Re-Test Ready")) / COUNTIFS(CHILDREN(), @cell <> "")) * 100) + "%"))
Tanks for your Help
Best Answers
-
Paul Newcome ✭✭✭✭✭✭
What about this:
=IFERROR(ROUND((COUNTIFS(CHILDREN(), OR(@cell = "Pass", @cell = "Fail-4Front Review", @cell = "Fail-Shorr Review", @cell = "Re-Test Ready")) / COUNTIFS(CHILDREN(), @cell <> "")) * 100) + "%", "")
-
Kaleb ✭✭✭
-
Kaleb ✭✭✭
@Paul NewcomeDisregard! User Error! Thanks
Answers
-
Andrée Starå ✭✭✭✭✭✭
Hi@Kaleb
I hope you're well and safe!
Try something like this.
=IFERROR(ROUND((COUNTIFS(CHILDREN(), OR(@cell = "Pass", @cell = "Fail-4Front Review", @cell = "Fail-Shorr Review", @cell = "Re-Test Ready")) / COUNTIFS(CHILDREN(), @cell <> "")) * 100) + "%"),""
Did that work/help?
I hope that helps!
Be safe, and have a fantastic weekend!
Best,
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
✅我的帖子(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.
-
Danielle Arteaga ✭✭✭✭✭✭
Hi, Kaleb.
What do you want to have happen if the formula results in an error? Do you want the cell to be blank? If so, this should work:
IFERROR(ROUND((COUNTIFS(CHILDREN(), OR(@cell = "Pass", @cell = "Fail-4Front Review", @cell = "Fail-Shorr Review", @cell = "Re-Test Ready")) / COUNTIFS(CHILDREN(), @cell <> "")) * 100) + "%"), "")
-
Kaleb ✭✭✭
Blank, If $ Cell Does Not Equal One of Those Statuses:
@cell = "Pass", @cell = "Fail-4Front Review", @cell = "Fail-Shorr Review", @cell = "Re-Test Ready
-
Kaleb ✭✭✭
Same Error.
The Original Formula Results in Divide By Zero Because None of The Statuses Equal Pass, Fail-4Front Review, Fail-Shorr Review, Re-Test Ready
-
Danielle Arteaga ✭✭✭✭✭✭
Does your formula work without the "IFERROR" wrapper? If the only error you are getting is #DIVIDE BY ZERO, then the IFERROR wrapper should work as formatted in my original answer. If you expect a non-zero value as a result of the formula operation but get an error like #UNPARESEABLE, the issue isn't with the IFERROR wrapper. My suspicion is that the error is coming from the @cell references, because those aren't associated to a specific row/column. Tough to say without seeing the structure of your sheet.
-
Paul Newcome ✭✭✭✭✭✭
What about this:
=IFERROR(ROUND((COUNTIFS(CHILDREN(), OR(@cell = "Pass", @cell = "Fail-4Front Review", @cell = "Fail-Shorr Review", @cell = "Re-Test Ready")) / COUNTIFS(CHILDREN(), @cell <> "")) * 100) + "%", "")
-
Kaleb ✭✭✭
Thanks@Paul NewcomeThat Worked. I See I was Missing a Comma in This Part of The Formula: * 100) + "%",
-
Kaleb ✭✭✭
-
Kaleb ✭✭✭
@Paul NewcomeDisregard! User Error! Thanks
Help Article Resources
Categories
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/105736/\")<\/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":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":37,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+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-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"