Help with multiple IFERROR statements

Options
jmo
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

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • jmo
    jmo ✭✭✭✭✭✭
    Options

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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 01/09/21
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • jmo
    jmo ✭✭✭✭✭✭
    Options

    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
    Mark Cronk ✭✭✭✭✭✭
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • jmo
    jmo ✭✭✭✭✭✭
    Options

    Hmm still unparseable

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • jmo
    jmo ✭✭✭✭✭✭
    Options

    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
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • jmo
    jmo ✭✭✭✭✭✭
    Options

    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
    Mark Cronk ✭✭✭✭✭✭
    Options

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!