Date Formula- 60 days out

Hi there, I have this formula that I am trying to create on my metrics sheet.

Goal: A formula that counts the instances where the state is not empty and is "Alabama", and where the date is before 60 days

I have a lengthy archive and want to see metrics from the last 60 days of form submission date.

Formula I have: =COUNTIFS({State}, "<>"{Alabama}, {Date}, ">", TODAY() + 60)

My references are populating for State, Alabama and Date. I keep getting an error though.

Date Column (That is referenced) is a date column: I would only want the metric to count the 3 2/14 date rows.


Best Answer


  • John_Foster
    John_Foster ✭✭✭✭✭✭

    Hi @Brittanyy,

    I think the formula you want is below.

    =COUNTIFS({State}, "<>"{Alabama}, {Date}, ">", TODAY(-60))

    This should then always show you data from the last 60 days.

    Hope this helps!


  • Brittanyy
    Brittanyy ✭✭✭✭
    edited 03/04/24

    Thanks @John Foster

    I tried the formula, but I got a Unparseable even though its grabbing my references.

    For a bit more of a context on my references:

    the "State" column in our dataset allows for multiple selections, enabling users to choose more than one state. To facilitate our state count calculations, I've created helper columns for all 50 states in the referenced sheet. These helper columns populate with the respective state name if it's selected in the "State" column.

    For instance, in the "Alabama" helper column, I've employed the following formula:

    =IF(OR(CONTAINS("Alabama", State@row), CONTAINS("Alabama", State@row)), "Alabama", "")

    This formula populates "Alabama" in the helper column if it's selected in the "State" column.

    Furthermore, the "Date" column automatically records dates associated with each entry.

    The original formula I used that worked: =COUNTIFS({State}, "<>", {Alabama}, "Alabama").

    However, to enhance our dashboard's functionality, I'm now looking to incorporate additional logic into the formula. Specifically, I aim to count the occurrences where "Alabama" is selected in the State column and is populated in the " Alabama" column and date column is in the last 60 days

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Brittanyy

    I hope you're well and safe!

    Try something like this.

    =COUNTIFS({State}, <> "Alabama", {Date}, >, TODAY() + 60)

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic weekend!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!