Need to return a count of dates from last week in the current year

Options
Anna Estenzo
Anna Estenzo ✭✭
edited 06/20/22 in Formulas and Functions

Hello,

I'm trying to return a count of how many dates in a column were from last week in the current year. I was using this formula =COUNTIF([Date Step 3 Completed]:[Date Step 3 Completed], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1), but I noticed that it started pulling last week's count from 2021 and 2022. I just want it to pull last week's count from 2022.

I'm trying this formula:

=COUNTIFS([Date Step 3 Completed]:[Date Step 3 Completed], IFERROR(YEAR(@cell), 0) = YEAR(TODAY())), IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1)

But it is flagging as #UNPARSABLE.

Any help would be greatly appreciated.

Thank you!

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Anna Estenzo

    Try this

    =COUNTIFS([Date Step 3 Completed]:[Date Step 3 Completed], IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), [[Date Step 3 Completed]:[Date Step 3 Completed], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1)

    Does this work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Anna Estenzo

    Try this

    =COUNTIFS([Date Step 3 Completed]:[Date Step 3 Completed], IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), [[Date Step 3 Completed]:[Date Step 3 Completed], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1)

    Does this work for you?

    Kelly

  • Anna Estenzo
    Options

    Yes! Thank you so much!! There was an extra bracket in front of the second range, but once I removed that it worked!

    =COUNTIFS([Date Step 3 Completed]:[Date Step 3 Completed], IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), [Date Step 3 Completed]:[Date Step 3 Completed], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1)

    Thanks, Annie

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!