# SubTasks Due in 7 Days

Options
✭✭✭✭✭
edited 05/16/24

Need help with a formula to determine how many subtasks are due in 7 days that are not complete.

Columns are Finish (which is a date), Status <> Complete. I also have a Helper Parent Column that has no data (blank) for the main Tasks. Had a formula that used to work but no longer is producing the correct result.

• ✭✭✭✭✭
Options

@Brad Klodowski I got it to work, with one more piece of criteria. In my original formula I had to add Status<>Complete

I went from 12 to 10 (the correct number) with this formula:

=COUNTIFS(Finish:Finish, <=TODAY(+7), Finish:Finish, >=TODAY(), [Helper Parent Column]:[Helper Parent Column], <>"", Status:Status, <>"Complete")

Still like your Count Formula. Thanks for your help.

• ✭✭✭✭✭✭
Options

Are you able to provide a screenshot for context?

• Options

2 ways to do this, depending on how you want to do it:

1. COUNTIFS - This is probably the simplest solution, write a COUNTIFS formula which checks the following:
1. Finish Date within 7 days
1. Range: [Finish Date]:[Finish Date]
2. Criteria: AND(@cell < TODAY()+7, @cell > TODAY())
2. Status is not complete
1. Range: Status:Status
2. Criteria: @cell <> "Complete"
3. The finished formula might look like (I'm assuming some column names here)
1. =COUNTIFS([Finish Date]:[Finish Date], AND(@cell < (TODAY() + 7), @cell > TODAY()), Status:Status, @cell <> "Complete")
2. You can also add some additional criteria, e.g. to narrow down to a specific person.
2. COUNT(COLLECT) - This is a bit more complicated, will accomplish the same thing, and can be way more powerful in the future / other scenarios. COLLECT has some different syntax, where you choose what you're collecting first, then specify ranges and criteria similar to COUNTIFS.
1. Range to collect - this can be anything that has a value for every row, I usually will use Task Name
2. Finish Date within 7 days
1. Range: [Finish Date]:[Finish Date]
2. Criteria: AND(@cell < TODAY()+7, @cell > TODAY())
3. Status is not complete
1. Range: Status:Status
2. Criteria: @cell <> "Complete"
4. The finished formula might look like:
1. =COUNT(COLLECT([Task Name]:[Task Name], [Finish Date]:[Finish Date], AND(@cell < (TODAY() + 7), @cell > TODAY()), Status:Status, @cell <> "Complete"))

Either of these should work - happy to help troubleshoot further if they don't.

• ✭✭✭✭✭
Options

@Brad Klodowski Both tips above seem logical. They do not work for me. Today, I have 10 subtasks due. The formulas supplied above are returning a 0 value.

• edited 05/17/24
Options

@PMOGal any chance you can provide a screenshot of the data you're working with? I've tested both formulas as I wrote them in a testing sheet I have and both seem to work, so it might be something we need to work out with your data set.

• ✭✭✭✭✭
Options

@Brad Klodowski I got it to work, with one more piece of criteria. In my original formula I had to add Status<>Complete

I went from 12 to 10 (the correct number) with this formula:

=COUNTIFS(Finish:Finish, <=TODAY(+7), Finish:Finish, >=TODAY(), [Helper Parent Column]:[Helper Parent Column], <>"", Status:Status, <>"Complete")

Still like your Count Formula. Thanks for your help.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!