Formula to Count the Number of Tasks that will be due within the next 5 days

sstarr

Hello,

I've having difficulty getting a COUNTIFS formula to work correctly, I'm not sure what is wrong with my syntax.

I'm trying to return the count/number of tasks that will be coming due within the next 5 days for a particular project. I've tried a number of variations (listed below), but the count received never matches what actually reflects in the sheet, or what would be returned if a ran a report.

=COUNTIFS([Assigned To]:[Assigned To], "Person", [Status]:[Status], <>"Complete", [End Date]:[End Date], <TODAY(+5))

=COUNTIFS([Assigned To]:[Assigned To], "Person", [Status]:[Status], <>"Complete", [End Date]:[End Date], <=TODAY(+5))

=COUNTIFS([Assigned To]:[Assigned To], "Person", [Status]:[Status], <>"Complete", [End Date]:[End Date], <=TODAY(5))

etc. etc. Any suggestions?

Also, in a perfect world, the total would not include tasks counted to be due that day, but I was just trying to get any variation to work.

Paul Newcome

Could the [Assigned To] column have multiple people listed in the same cell? Are there any rows where the Status is not Complete and the End Date is in the past?

Jeana

Interesting...I'm having a similar issue with a Summary field. I can filter my sheet for anything due in the next 7 days and there are items that meet that criteria. However, this formula returns 0.

=COUNTIF([Due Date for Asset Review]:[Due Date for Asset Review], TODAY(+7))

This one doesn't work either -

=COUNTIFS([Request Status]:[Request Status], AND(@cell = "In Progress"), [Due Date for Asset Review]:[Due Date for Asset Review], TODAY(+14))

Paul Newcome

@Jeana Your first formula is only looking for dates that are 7 days out. Not within the next 7 days. Your second formula has an unnecessary AND statement, and you are doing the same thing with your dates by looking for dates that are equal to two weeks from today and not necessarily within the next two weeks.

For dates that are greater than today and less than or equal to seven days from today (within the next 7 days but not in the past):

=COUNTIFS([Due Date for Asset Review]:[Due Date for Asset Review], AND(@cell >= TODAY(), @cell <= TODAY(+7)))

For within the next 14 days but not in the past with a status of "In Progress":

=COUNTIFS([Request Status]:[Request Status], "In Progress", [Due Date for Asset Review]:[Due Date for Asset Review], AND(@cell >=TODAY(), @cell <= TODAY(+14)))

Jeana

Duh, thanks Paul. I wasn't considering that it needed to be a RANGE.

Thank you!

Paul Newcome

@Jeana Happy to help! 👍️

@sstarr If the issue isn't because of multiple names in the [Assigned To] column, the discrepancy could be a result of what was just worked out above: Establishing a range of dates to pull. If there are not multiple name in the [Assigned To] column and the date range bit isn't the solution, there are a few other things we can take a look at. Feel free to let me know.

ON

Please help, I'm trying to follow this thread but get "INVALID DATA TYPE".

I have a master sheet that several people use. There re a number of tasks assigned to me. I want stats for a dashboard that tells me how many tasks assigned to me are due in the next 7 days and have created a "support sheet" to pull the data into.

I already have items due "Today" working with multiple variables. My issue is those same variables AND a date range.

My formula is:

=COUNTIFS({Workplan Assigned to}, $[Assigned to]$1, {Workplan Status}, <>[Is Not Status]@row, AND({Workplan Due Date}, Today@row >= TODAY(), Today@row <= TODAY([In the next (days)]@row)))

My search ranges are:

{Workplan Assigned to} = the Assignee column in the master "Workplan" Sheet (type: Contact List - single entry)

{Workplan Status} = the Status column in the master "Workplan" Sheet (Type: Dropdown list single entry)

{Workplan Due Date} = You've probably noticed a trend... the Finish date column in the master "Workplan" Sheet (Type: Date Column)

Return the count for the number of rows in a different sheet that matches the following criteria:

• Match/count name of the assignee that match name in "Assigned to" row of the support sheet.
• Match/count all rows that are NOT "complete" status
• Due in the next "7" days

What am I doing wrong? any pointers most welcome!

Paul Newcome

@ON

Your AND function was misplaced.

=COUNTIFS({Workplan Assigned to}, $[Assigned to]$1, {Workplan Status}, <>[Is Not Status]@row, {Workplan Due Date}, AND(Today@row >= TODAY(), Today@row <= TODAY([In the next (days)]@row)))

ON

@Paul Newcome Thank you for the reply!!

Your formula correction provided a "0" in the cell which is progress! :-) however, there should be 11 tasks due in the next seven days (the output is being checked against a regular SmartSheet report with the same variables - I also slipped in a number of dummy entries one matches the criteria this formula is trying to return)

That is a snap from the master sheet with the dummy records. Has Name, Date and status in "range"

Paul Newcome

Try putting this in a date type column and see what it returns...

=TODAY([In the next (days)]@row)

ON

@Paul Newcome It returns a date 7 days from today. I did test the above (original) formula with {range} <=TODAY(7) as opposed to the cell reference.

Paul Newcome

What is in the Today column (Today@row)?

ON

@Paul Newcome It's a column called "Today" that has =TODAY() in the first cell which just returns today's date.

I use it as a reference cell for a "today's date" metric widget on a dashboard.

I've included it in the formula as the examples above my first comment reference @cell so ASSumed I needed to reference a date to check against

Paul Newcome

Try this one...

=COUNTIFS({Workplan Assigned to}, $[Assigned to]$1, {Workplan Status}, <>[Is Not Status]@row, {Workplan Due Date}, AND(@cell >= TODAY(), @cell <= TODAY([In the next (days)]@row)))

ON

@Paul Newcome I've cheated...

Created a new date column called "Next Week" in

Cell 1: =TODAY()

Cell 2: =TODAY(1)

Cell 3: =TODAY(2)

etc to 7 days beyond today.

And then used the following formula in my "Days" Column

=COUNTIFS({Workplan Due Date}, [Next Week]@row, {Workplan Assigned to}, $[Assigned to]$1, {Workplan Status}, <>$[Is Not Status]$1)

And then used =SUM([Next Week]1:[Next Week]8) to get a total (incl those due today).

Not pretty but its a support sheet. Would love to fix the formula but don't ruin your day trying to fix it :-) Thank you so much for your help!!!

ON

https://community.smartsheet.com/discussion/comment/276000#Comment_276000

@Paul Newcome YOU ARE A LEGEND!!!!!

Didn't see this before posting my "cheat" above... your last formula works like a charm!! Thank you so much!!