Help with multiple IFERROR statements

jmo

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

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

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,

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

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