Count What is Due for Today and the next 7 days

I have a summary sheet where I want to count the incomplete tasks due by company for the next 7 days. The formula below counts 0 where there are three. If I remove the 7 it counts everything incomplete. What should I do with the TODAY to make it count tasks for today AND the next 7 days?

Thanks!


=COUNTIFS({Company}, [Company]@row, {Finish Date}, >=TODAY(7), {Completed}, "No")

Best Answer

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓

    I think the problem is in how you're managing your Finish Date. I don't think it is resolving as you think it is. It reads that it will require the Finish Date to be = or greater than 7 days from now. I think what you want is this:

    =COUNTIFS({Company}, [Company]@row, {Finish Date}, AND(@cell >=TODAY(), @cell < TODAY(8)), {Completed}, "No")

    This should put the date in the range you're looking for.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!