Adding IFERROR Formula

Kaleb · 05/26/23

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

Paul Newcome · 05/26/23

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 · 05/26/23

@Paul Newcome I Spoke Pre-Maturely. Pass is being Calculated but The Other Status Are Not. See Screenshot

Kaleb · 05/31/23

https://community.smartsheet.com/discussion/comment/377796#Comment_377796

@Paul Newcome Disregard! User Error! Thanks

Andrée Starå · 05/26/23

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 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!

Danielle Arteaga · 05/26/23

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 · 05/26/23

https://community.smartsheet.com/discussion/comment/377762#Comment_377762

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 · 05/26/23

https://community.smartsheet.com/discussion/comment/377760#Comment_377760

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 · 05/26/23

@Kaleb

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 · 05/26/23

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) + "%", "")