Formula Help With Date Range look back

Eric Fischer
Eric Fischer ✭✭
edited 04/18/23 in Formulas and Functions

I have a sheet that I am to trying to get a count based on specific response in a column and be based on completion in the last 30 days.

Answers

  • Lachlan Stead
    Lachlan Stead ✭✭✭✭✭✭

    Below is a basic example based on random columns but it should give you an idea:

    =COUNTIFS([Response Column]:[Response Column], [Column7]@row, [Column1]:[Column1], <=TODAY(0), [Column1]:[Column1], >=TODAY(-30))

  • Sorry i am horrible at this. Would you have specific formula for this below. I want all responses of concerns in the column labeled resident choice to be counted when the date is within the last 30 days.

    Thanks in advance.


  • Genevieve P.
    Genevieve P. Employee Admin
    edited 05/11/23

    Hi @Eric Fischer

    Were you able to figure this out?

    Here's the original formula updated with your column names, if you were just looking for "No Issues":

    =COUNTIFS([Resident Choice]:[Resident Choice], "No Issues", Date:Date, <=TODAY(0), Date:Date, >=TODAY(-30))

    Then if you're looking for a different value you can change it in the quotes:

    =COUNTIFS([Resident Choice]:[Resident Choice], "UTA", Date:Date, <=TODAY(0), Date:Date, >=TODAY(-30))


    These formulas are for using in a Summary Field (so in the same sheet as the data). However you can replace a [column reference] with a {cross sheet reference} if you're building this in a secondary sheet:

    =COUNTIFS({Resident Choice column}, "No Issues", {Date Column}, @cell <=TODAY(0), {Date Column}, @cell >=TODAY(-30))


    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!