Error formula to also include blanks for zeros

Stacey Carlsen

Hi - I currently have a formula wrapped with the IFERROR to return blank if an error is calculated. I would also like it to be wrapped to return a blank for zeros as well. Any thoughts? Here is my formula:

=IFERROR(COUNTIFS({OS}, OS@row, {Level}, "Unify", {Project Status}, "Complete", {Project Type}, "Database", {Completion Date}, AND(IFERROR(YEAR(@cell), "") = PK$13)), "")

Paul Newcome

Try something like this...

=IFERROR(IF(COUNTIFS({OS}, OS@row, {Level}, "Unify", {Project Status}, "Complete", {Project Type}, "Database", {Completion Date}, AND(IFERROR(YEAR(@cell), "") = PK$13) > 0, COUNTIFS({OS}, OS@row, {Level}, "Unify", {Project Status}, "Complete", {Project Type}, "Database", {Completion Date}, AND(IFERROR(YEAR(@cell), "") = PK$13))), "")

Stacey Carlsen

Hi Paul - thanks. I tried this, but I'm getting a #incorrect argument set error.

Paul Newcome

Ok. Here is the idea behind it (and I will redo it in steps this time to make sure I didn't miss something the first time).

First we take your original COUNTIFS:

COUNTIFS({OS}, OS@row, {Level}, "Unify", {Project Status}, "Complete", {Project Type}, "Database", {Completion Date}, AND(IFERROR(YEAR(@cell), "") = PK$13))

And now I see a little tweak to be made. We don't need the AND statement, so here it is trimmed down a touch...

COUNTIFS({OS}, OS@row, {Level}, "Unify", {Project Status}, "Complete", {Project Type}, "Database", {Completion Date}, IFERROR(YEAR(@cell), "") = PK$13)

Now we take our COUNTIFS and say that if it is greater than zero, run it. Leaving the 3rd portion of the IF statement out will automatically generate a blank for anything that is not greater than zero.

IF(COUNTIFS({OS}, OS@row, {Level}, "Unify", {Project Status}, "Complete", {Project Type}, "Database", {Completion Date}, AND(IFERROR(YEAR(@cell), "") = PK$13)) > 0, COUNTIFS({OS}, OS@row, {Level}, "Unify", {Project Status}, "Complete", {Project Type}, "Database", {Completion Date}, AND(IFERROR(YEAR(@cell), "") = PK$13)))

Now we drop that back into the IFERROR statement, and that should be working.

=IFERROR(IF(COUNTIFS({OS}, OS@row, {Level}, "Unify", {Project Status}, "Complete", {Project Type}, "Database", {Completion Date}, AND(IFERROR(YEAR(@cell), "") = PK$13)) > 0, COUNTIFS({OS}, OS@row, {Level}, "Unify", {Project Status}, "Complete", {Project Type}, "Database", {Completion Date}, AND(IFERROR(YEAR(@cell), "") = PK$13))), "")