Where do I put the IFERROR function to correct for year?

Carroll Wall
Carroll Wall ✭✭✭✭✭
edited 01/24/24 in Formulas and Functions

I have a column of dates. New rows are being added over time.

I count how many occur in each month.

In a separate metric sheet, have 12 formulas, 1 for each month, that look like this.

=COUNTIFS({Date Column}, IFERROR(MONTH(@cell), 0) = 1, {Date Column}, MONTH(@cell) = 1) for January

=COUNTIFS({Date Column}, IFERROR(MONTH(@cell), 0) = 2, {Date Column}, MONTH(@cell) = 2) for February, etc.

However, now that it is a new year, I want to exclude 2023 dates so I think I need to add an IFERROR and a YEAR(TODAY()) function,

Something like IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

but I am getting #unparseble.

Your insights would be greatly appreciated, my Smartsheet Community friends!

Stay Safe!

Carroll

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You can simplify the original COUNTIFS by getting rid of the second range/criteria set as it is redundant of the first.

    =COUNTIFS({Date Column}, IFERROR(MONTH(@cell), 0) = 1, {Date Column}, MONTH(@cell) = 1)

    =COUNTIFS({Date Column}, IFERROR(MONTH(@cell), 0) = 1)


    Then you can use an AND statement to add the YEAR argument like so:

    =COUNTIFS({Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))


    Trying to add exactly what you have in your original post of

    IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

    has one too many closing parenthesis at the end.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You can simplify the original COUNTIFS by getting rid of the second range/criteria set as it is redundant of the first.

    =COUNTIFS({Date Column}, IFERROR(MONTH(@cell), 0) = 1, {Date Column}, MONTH(@cell) = 1)

    =COUNTIFS({Date Column}, IFERROR(MONTH(@cell), 0) = 1)


    Then you can use an AND statement to add the YEAR argument like so:

    =COUNTIFS({Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))


    Trying to add exactly what you have in your original post of

    IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

    has one too many closing parenthesis at the end.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Carroll Wall
    Carroll Wall ✭✭✭✭✭

    Thanks for the speedy reply, Paul. It worked!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!