IFERROR + IF/ISBLANK

Options
Marvin Daniels
Marvin Daniels ✭✭✭✭✭
edited 08/25/22 in Formulas and Functions

This formula is almost perfectly pulling downtime by examiner and production date:

=IFERROR(INDEX(COLLECT({downtime - downtime}, {downtime - production date}, [Production Date]@row, {downtime - examiner}, Examiner@row), 1), 0)

There are a few records that are not in error, but the result is a blank, however, I need those blanks recorded as a zero. I have tried a number of ways to wrap this formula in an IF/ISBLANK with no success. I'm sure I'm missing something simple, but what is the trick to combining IF/ISBLANK/IFERROR?

At this point, I've added the above in a helper column. The downtime column with the IF/ISBLANK logic points to the helper, but I'd like to eliminate the helper column, if possible.

I have another sheet where I'm doing something similar and I need to note blank or error, so understanding this will help me with a few projects. Thanks in advance!

Tags:

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Try this:


    =IF(IFERROR(INDEX(COLLECT({downtime - downtime}, {downtime - production date}, [Production Date]@row, {downtime - examiner}, Examiner@row), 1), 0) <> "", IFERROR(INDEX(COLLECT({downtime - downtime}, {downtime - production date}, [Production Date]@row, {downtime - examiner}, Examiner@row), 1), 0), "")

  • Marvin Daniels
    Marvin Daniels ✭✭✭✭✭
    Options

    Thank you @Paul Newcome!

    I made one minor change. I removed the last "" in favor of 0, which is what I need for this formula:

    =IF(IFERROR(INDEX(COLLECT({downtime - downtime}, {downtime - production date}, [Production Date]@row, {downtime - examiner}, Examiner@row), 1), 0) <> "", IFERROR(INDEX(COLLECT({downtime - downtime}, {downtime - production date}, [Production Date]@row, {downtime - examiner}, Examiner@row), 1), 0), 0)

    In the same sheet we need to look up up the examiner, however, we need to know if the result is missing or blank, so I adapted the formula with this INDEX/MATCH. Being a little funny with "doesn't matter", but it really doesn't.

    =IF(IFERROR(INDEX({examiner - xref}, MATCH([User Code]@row, {eldouser}, 0)), "doesn't matter") <> "", IFERROR(INDEX({examiner - xref}, MATCH([User Code]@row, {eldouser}, 0)), "cross reference missing"), "cross reference blank")

    Thanks for help with this sheet, but really thanks for putting it out here for everyone. I cannot be the only one to struggle with this one.

  • CO_Angela
    Options

    Thank you for sharing this. I was stuck on this on.

  • CO_Angela
    Options

    Thank you for sharing this. I was stuck on this one.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!