IFERROR Formula Help

Lila Carsten

I would like to be able to count symbols in a column, even if there is an "#Invalid Data Type" error in one or more of the cells in that column. Will the =IFERROR formula help me?

Here's what I'm working on:

I am trying to determine whether or not a project has been completed early, on time, or late based on the date it was requested to be complete and the actual completion date. I have assigned symbols in a "P STAT" column... green symbol as early, yellow is on time, and red is late. If the project is not yet completed, the cell returns an #INVALID DATA TYPE error and because that error is present in the P STAT column, the entire column will not count the rest of the symbols. I have been manually deleting the error cells, then filling them in once that project date is complete, however, that's a lot of manual maintenance. I'd like a solution to disguise the error message with another symbol, or return a value that will allow the column to be counted... this is what I was thinking:

=IF(DATEONLY([Install Request On/Before]49) = DATEONLY([Production Complete Date]49), "Yellow", IF(DATEONLY([Install Request On/Before]49) < DATEONLY([Production Complete Date]49), "Red", IF(DATEONLY([Install Request On/Before]49) > DATEONLY([Production Complete Date]49), "Green", IFERROR([P STAT]:[P STAT]49), "Blue")))

Am I on the right track? This particular formula is not working, but I wasn't sure if this was the right way to accomplish what I am looking to accomplish. Any advice on this would be great appreciated, thank you!

Shaine Greenwood

Hi Lila,

You're on the right track; your IFERROR function needs to be in front of the rest of your formula. The syntax is like this:

=IFERROR(YOUR FORMULA, "Blue")

Details on IFERROR are available in our Help Center: https://help.smartsheet.com/function/iferror

Another great overall reference is our full functions list: https://help.smartsheet.com/functions

 

Lila Carsten

Thank you Shaine,

I put the IFERROR function at the front so now my formula reads:

=IFERROR(DATEONLY([Install Request On/Before]98) = DATEONLY([Production Complete Date]98), "Yellow", IF(DATEONLY([Install Request On/Before]98) < DATEONLY([Production Complete Date]98), "Red", IF(DATEONLY([Install Request On/Before]98) > DATEONLY([Production Complete Date]98), "Green", "Blue")))

This is returning an #INCORRECT ARGUMENT SET error this time. I looked into the sites you recommended... I'm pretty new at formula writing so I'm not sure where to go from here. 

Shaine Greenwood

Hi Lila—

Looks like you're almost there, but you're missing an IF statement at the front and your parens are slightly off. Try this:

=IFERROR(IF(DATEONLY([Install Request On/Before]98) = DATEONLY([Production Complete Date]98), "Yellow", IF(DATEONLY([Install Request On/Before]98) < DATEONLY([Production Complete Date]98), "Red", IF(DATEONLY([Install Request On/Before]98) > DATEONLY([Production Complete Date]98), "Green"))), "Blue")

Copying and pasting this may give you an error. If you get an error after copying and pasting this formula, try manually typing it in exactly as is.