# COUNTIFS formula for tasks due between 8 and 14 days in future

Options
✭✭✭✭✭

I'm trying to write a formula to count the number of tasks due between 8 and 14 days in the future. I started with a formula that is working for the number of tasks due in 7 days:

=COUNTIFS({Schedule - % Complete}, <1, {Schedule - End Date}, <=TODAY(+7))

I could repeat the formula, replacing the +7 with +14, but I don't want the tasks due in 7 days to be repeated in the due-in-14-days number. I tried this formula, but I get invalid operation.

=COUNTIFS({Schedule - % Complete}, <1, {Schedule - End Date}, >TODAY(+7), <=TODAY(+14)).

Thank you for any help you can provide.

Lori Flanigan

Tags:

## Answers

• Employee
Options

Hello @loreleijflanigan112461 ,

I was able to create an example Formula that should provide you with the desired result. Please note that where I have Column Name References, you would instead use your Sheet Reference Names.

The Formula I used was: =COUNTIFS([Schedule - % Complete]:[Schedule - % Complete], <1, [Schedule - End Date]:[Schedule - End Date], >=TODAY(+7), [Schedule - End Date]:[Schedule - End Date], <=TODAY(+14))

Here's the Output of this Formula:

When using date ranges within a Formula, you must reference the Date Range for both the Greater Than, and the Less Than " [Schedule - End Date]:[Schedule - End Date], >=TODAY(+7), [Schedule - End Date]:[Schedule - End Date], <=TODAY(+14))"

Let me know if you have any questions!

Regards

Sean

• ✭✭✭✭✭
Options

Thanks for responding, Sean. I entered the example formula you provided with my sheet reference names. I'm getting an #UNPARESEABLE error message.

Lori

• Employee
Options

Are you able to provide screenshots of what you are currently seeing, whilst removing any sensitive data?

Kindest Regards

Sean

• ✭✭✭✭✭
Options

We got the formula to work. Here is the final version:

=COUNTIFS({Schedule - % Complete}, <1, {Schedule - End Date}, >=TODAY(+8), {Schedule - End Date}, <=TODAY(+14))

Thanks!

Lori

• Employee
Options

Awesome!

Glad its working now

Regards

Sean

• ✭✭
Options

Hello, I'd like to use this formula in a Summary field, but it seems to be including weekends. I have excluded Sat/Sun in the schedule, but how do I exclude weekends in this formula?

• Employee
edited 11/18/22
Options

Hi @1Andi

You're right, the date range of 8 - 14 days is 7 days through a weekend, dependent on Today's date (even if that day is a Saturday or Sunday).

For your formula, can you clarify what your sheet set-up is like (screen captures would be ideal), and what exactly you're looking to count?

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• ✭✭
edited 11/18/22
Options

Thank you for the quick response. I'm trying calculate the number of tasks that (1) have a have a value of "Ops" in one column, (2) have only open statuses, and (3) have an end date in another column that are between two days in the future. In this case, counts of Ops tasks due between 5-10 days from now. My formula works at day 6 but then stops. It doesn't include days 7-10. And, I would like to exclude weekends.

=COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Cancelled", Stream:Stream, "OPS", [End Date]:[End Date], >=TODAY(6), [End Date]:[End Date], <=TODAY(11))

• Employee
Options

Hi @1Andi

It looks like you're close! What I would do here is use the WORKDAY function instead of just TODAY(days).

For example:

=COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Cancelled", Stream:Stream, "OPS", [End Date]:[End Date], >=WORKDAY(TODAY(), 6), [End Date]:[End Date], <=WORKDAY(TODAY(), 11))

This should count the rows that have a date equal-to-or-between 6 - 11 working days, based on today's date. Let me know if that worked for you!

Cheers,

Genevieve

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• ✭✭
Options

That fixed it - thank you!!!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!