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 ✭✭✭✭
@Paul NewcomeI Spoke Pre-Maturely. Pass is being Calculated but The Other Status Are Not. See Screenshot
-
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
✅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.
-
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 ✭✭✭✭
空白,如果细胞不等于美元该队ses:
@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 ✭✭✭✭
@Paul NewcomeI Spoke Pre-Maturely. Pass is being Calculated but The Other Status Are Not. See Screenshot
-
Kaleb ✭✭✭✭
@Paul NewcomeDisregard! User Error! Thanks
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-27T01:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"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-27T01:32:22+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":23,"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":"
![https:\/\/us.v-cdn.net\/6031209\/uploads\/AASP3RZXCA0C\/image.png<\/a><\/p>https:\/\/us.v-cdn.net\/6031209\/uploads\/DAQ0469KOWLL\/image.png<\/a><\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Paul Newcome\",\"userID\":45516}}},{\"insert\":\" I Spoke Pre-Maturely. Pass is being Calculated but The Other Status Are Not. See Screenshot\\n\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/AASP3RZXCA0C\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/AASP3RZXCA0C\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/DAQ0469KOWLL\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/DAQ0469KOWLL\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2023-05-26T21:34:47+00:00","insertUser":{"userID":146904,"name":"Kaleb","url":"https:\/\/community.smartsheet.com\/profile\/Kaleb","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hzlAoSbUIDs!hU_D4f686H8!MeM8OksBL1v","dateLastActive":"2023-08-26T15:14:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/377796#Comment_377796","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/377796#Comment_377796](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/AASP3RZXCA0C\/image.png\"){kind=link}
![https:\/\/us.v-cdn.net\/6031209\/uploads\/DAQ0469KOWLL\/image.png<\/a><\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Paul Newcome\",\"userID\":45516}}},{\"insert\":\" I Spoke Pre-Maturely. Pass is being Calculated but The Other Status Are Not. See Screenshot\\n\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/AASP3RZXCA0C\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/AASP3RZXCA0C\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/DAQ0469KOWLL\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/DAQ0469KOWLL\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2023-05-26T21:34:47+00:00","insertUser":{"userID":146904,"name":"Kaleb","url":"https:\/\/community.smartsheet.com\/profile\/Kaleb","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hzlAoSbUIDs!hU_D4f686H8!MeM8OksBL1v","dateLastActive":"2023-08-26T15:14:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/377796#Comment_377796","embedType":"quote"}">](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/DAQ0469KOWLL\/image.png\"){kind=link}