Error formula to also include blanks for zeros

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

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.