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

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)).

Lori Flanigan

Tags:

• 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

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

Lori

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

Kindest Regards

Sean

• 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

• Awesome!

Regards

Sean

• 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?

• 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?

• 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))

• 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

• That fixed it - thank you!!!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!