How do I use CountIF to count items 30 days or less from Today without counting items in the past?

I have a CountIF formula set to count the number of cells that have dates 30 days or less from Today's date using the Today function, however it is also counting dates that are in the past. I only want to count the number of dates that are 30 days or less in the future.

Any way I can exclude past dates?

Here is what I am using for a formula:

=COUNTIF([Date Needed By]:[Date Needed By], <TODAY(+30))


Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/03/22 Answer ✓

    Try using COUNTIFS instead. You can specify dates less than or equal to 30 days in the future, and dates greater than or equal to today.

    =COUNTIFS([Date Needed By]:[Date Needed By], <=TODAY(+30), [Date Needed By]:[Date Needed By], >=TODAY(0))


    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    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!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!