Formula for counting tasks due in the next 7 days

jmacdal

Hello, I've created the below formula to count upcoming tasks that are due within the next 7 days. I prefer a single formula to also include "In Progress", "Not Started", or "On Hold" status and just planned on creating more countifs and summing them together. However, just testing the formula for Not Started, which I know there are 3 due in the next 7 days, it's retuning #UNPARSEABLE. When I remove the Due Date portion of the formula, I get the the total for every task in the project plan that's not started, so it has something to do with the way I have the Due Date.

=COUNTIFS([Row Type]:[Row Type], "Task", Status:Status, "Not Started", [Due Date]:[Due Date], AND(@cell <= TODAY(+7), @cell >= TODAY()))

Open to if maybe there is a better way to do this. I created a report and it pulls in 19 tasks due within the next 7 days (3 not started, 16 in progress). I just want the total count though as it's being displayed on a dashboard.

Appreciate in advance any ideas.

Jon Mark H

Hey @jmacdal!

Quick question while I look at this for you - what STATUSES do you have that you DON'T want calculated in this formula ("Complete", etc) - if it's not very many, it may be more efficient to make a negative statement in the formula rather than mention all the other statuses.

Let me know, and I'll be getting back to you either way!

-Jon

Jon Mark H

@jmacdal

Got it done for you, the issue with your posted formula was the "+" in the "TODAY(+7)" portion - in the future just use "TODAY(7)". The below formula will calculate all (3) of those Statuses as well.

=COUNTIFS([Row Type]:[Row Type], "Task", Status:Status, OR(@cell = "Not Started", @cell = "In Progress", @cell = "On Hold"), [Due Date]:[Due Date], AND(@cell <= TODAY(7), @cell >= TODAY()))

Let me know if that solves the problem or if you have any other needs!

-Jon

jmacdal

Hey Jon - that formula is returning #UNPARSEABLE. Great catch on where to place the 7, I ran a test on just that part of the formula and it returned a total but it included tasks that were "Complete" or Canceled"? So now the issue is upstream from the Due Date section of the formula.

Paul Newcome

The + won't throw the error.

The error is coming from the [Due Date]:[Due Date] reference itself. Double check you have spelled it to exactly match the column name in your sheet. One thing to keep in mind... Smartsheet will store multiple consecutive spaces but will only display a single space.

My tip is to click and drag a few cells within the column when creating that reference so you end up with something like this:

[Due Date]1:[Due Date]5

Then just delete the specific row numbers. This will guarantee that your column name is a good match.

jmacdal

The Due Date must have had a space or something as is not working, but it took me to do the steps that Paul mentioned as well, strange but again it's working. Appreciate both of your help!

Paul Newcome

Happy to help. 👍