Help with multiple IFERROR statements

Hi team - I have a sheet that has a "created" date that is auto generated. Now that we're into 2021 I need to differentiate 2021 vs 2020 months.

I used the following formula last year to get a count of something specific:

=COUNTIFS({(Source Data) Main row}, 1, {(Source Data) Exempt}, 0, {(Source Data) Created}, IFERROR(MONTH(@cell), 0) = 1)

But now that we're into 2021, how do I specify the same formula statement but include months with 2021 included?

Thanks,

Jeff

Find more posts tagged with

Accepted answers

Mark Cronk

Hi Jeff,

You got me curious enough to set up a test on my side. The formula works for me. I suspect you have an issue with one of your data ranges. I'd rebuild the formula and insert new ranges to confirm they are correct.

This is what worked for me. I can change the month, year or checkboxes and get the correct responses:

=COUNTIFS({(Source Data) Main row}, 1, {(Source Data) Exempt}, 0, {(Source Data) Created}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@Cell), 0)=2021))

{(Source Data) Main row} and {(Source Data) Exempt} need to be formatted as Checkbox columns. {(Source Data) Created} is a system generated Created column.

Good luck.

Mark

All comments

Mark Cronk

Hi Jeff,

Try:

=COUNTIFS({(Source Data) Main row}, 1, {(Source Data) Exempt}, 0, {(Source Data) Created}, AND(IFERROR(MONTH(@cell), 0) = 1, YEAR(@Cell)=2021))

Mark

jmo

@Mark Cronk - I get an UNPARSEABLE error when I copy/paste that in

Mark Cronk

Hi Jeff,

Try this much and see if it works. =COUNTIFS({(Source Data) Main row}, 1, {(Source Data) Exempt}, 0)

If it does, the issue is in: {(Source Data) Created}, AND(IFERROR(MONTH(@cell), 0) = 1, YEAR(@Cell)=2021)

Let me know what you find.

Mark

jmo

Hi @Mark Cronk - that portion worked by itself. Also, not sure if I was clear initially, but the full formula I used last year worked - all I had to do was change the MONTH number:

=COUNTIFS({(Source Data) Main row}, 1, {(Source Data) Exempt}, 0, {(Source Data) Created}, IFERROR(MONTH(@cell), 0) = 1)

So definitely a problem incorporating the AND(IFERROR(MONTH(@cell), 0) = 1, YEAR(@Cell)=2021) recommendation you provided.

Should that be written another way?

Mark Cronk

Good morning,

The syntax in the formula I provided looks right but let's try this instead:

=COUNTIFS({(Source Data) Main row}, 1, {(Source Data) Exempt}, 0, {(Source Data) Created}, IFERROR(MONTH(@cell), 0) = 1, {(Source Data) Created}, IFERROR(YEAR(@Cell), 0)=2021)

To get 2021 results you need to insert the criteria YEAR(@cell)=2021, where the cell is the date created on your external reference.

Work this time?

Mark

jmo

Hmm still unparseable

Mark Cronk

Very strange. The formula looks right. Is your {(Source Data) Created} range formatted as a date column? Confirm all of the range names are correct in the formula.

Maybe someone else can spot the error or has an idea about the cause.

Mark

jmo

Hi @Mark Cronk - it is definitely strange behavior. Yes, the (Source Data) Created) column is a system generated date column:

Here are some of the "source data" fields, to include the Created column:

Mark Cronk

Hi Jeff,

This is what worked for me. I can change the month, year or checkboxes and get the correct responses:

=COUNTIFS({(Source Data) Main row}, 1, {(Source Data) Exempt}, 0, {(Source Data) Created}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@Cell), 0)=2021))

{(Source Data) Main row} and {(Source Data) Exempt} need to be formatted as Checkbox columns. {(Source Data) Created} is a system generated Created column.

Good luck.

Mark

jmo

That last one worked, @Mark Cronk ! I had to manually input the formula step by step - it didn't work when I copy/pasted.

Thanks for sticking this out with me! 👍️

Mark Cronk

Perfect. Glad you found a solution. Thanks for contributing to the Community.

Mark

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.