IFERROR + IF/ISBLANK

Marvin Daniels

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!

Paul Newcome

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

Paul Newcome

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

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.

Paul Newcome

Happy to help. 👍️

CO_Angela

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

CO_Angela

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