Summary Sheet formula to count # of rows within 30 days of today

Options

Hi, I am trying to create a formula in a sheet summary that will count how many cells are within 30 days of today's date based on a date column. I can't figure it out. Can anyone help?


Thanks!

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    Try this if you're looking for in dates in the past 30 days, or if you're looking for dates in the next 30 days change the TODAY(-30) to TODAY(+30):

    =COUNTIF([Date Column]:[Date Column], >TODAY(-30))

    If you want to count rows with dates within 30 days in the past or within 30 days in the future:

    =COUNTIFS([Date Column]:[Date Column], >TODAY(-30)),[Date Column]:[Date Column], <TODAY(+30))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    Try this if you're looking for in dates in the past 30 days, or if you're looking for dates in the next 30 days change the TODAY(-30) to TODAY(+30):

    =COUNTIF([Date Column]:[Date Column], >TODAY(-30))

    If you want to count rows with dates within 30 days in the past or within 30 days in the future:

    =COUNTIFS([Date Column]:[Date Column], >TODAY(-30)),[Date Column]:[Date Column], <TODAY(+30))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Melanie Sanders
    Melanie Sanders ✭✭✭✭✭
    Options

    Thank you so much, Jeff. Worked like a charm!

    =COUNTIF([NEXT CONTACT]:[NEXT CONTACT], <TODAY(+30))

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    Glad I could help!

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Susan Kane
    Susan Kane ✭✭✭
    Options

    Hello,

    I have a similar problem, have a column "Date Closed" and want to add a Sheet Summary field that shows how many jobs have closed within the past 30 days. I've done a manual count and have 10 cells that meet this criteria. I've used the formula:

    =COUNTIF([Date Closed]:[Date Closed], <TODAY(-30))

    but it is returning a count of 0. What am I doing wrong?

    Anyone's help would be massively appreciated!

    Thanks


    Susan

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 07/13/20
    Options

    Hi @Susan Kane

    Your formula is checking for everything that is before 30 days ago.

    Try something like this.

    =COUNTIFS([Date Closed]:[Date Closed], >=TODAY(-30), [Date Closed]:[Date Closed], <=TODAY())
    

    Did that work?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | 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!