Help pulling data from certain date range


I am currently pulling data from a mainsheet into a dashboard via a metrics sheet. Its working fine but I have been asked to start only showing data from the last 30 days on the dashboard. I am having problems figuring out how to add a date formula to my current formulas. An example of my current formula is =COUNTIF({Project Tactic}, CONTAINS("Ad - Digital", @cell)). I would now like to add an additional portion that would pull from the same sheet via the created by date (or possibly a Due date column) that would show what was created in the last 30 days (or future 30 if go by due date). Can anyone help with the additional portion of the formula? Thank you.


Best Answer


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would first need to switch from a COUNTIF to a COUNTIFS with the S on the end to allow for multiple range/criteria sets. Then you would follow the same pattern to select your date range then specify the criteria.

    Created in the past 30 days:

    =COUNTIFS({Project Tactic}, CONTAINS("Ad - Digital", @cell), {Created Date Range}, >= TODAY(-30))

    Due in the next 30 days:

    =COUNTIFS({Project Tactic}, CONTAINS("Ad - Digital", @cell), {Due Date Range}, AND(@cell <= TODAY(30), @cell >= TODAY()))

  • Sheryl Walder
    Sheryl Walder ✭✭✭
    edited 12/02/20

    Thank you but they don't seem to work.

    Counted past 30 days, I changed the column to correct name: =COUNTIFS({Project Tactic}, CONTAINS("Ad - Digital", @cell), {Created}, [>=TODAY(-30)]) - it comes back with #unparseable

    Next 30 days, also have issue: =COUNTIFS({Project Tactic}, CONTAINS("Ad - Digital", @cell), {Due Date}, AND(@cell <= TODAY(30), @cell >= TODAY())) comes back with #incorrect argument set

    I am not sure what I am doing wrong.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!