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

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.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Happy to help. 👍️

Jessica Griffin

=COUNTIF({Template -Tasks Range 1 - Due Date}, <=TODAY(7)

I have this working, however how do I add Only if Status is not complete?

Paul Newcome

@Jessica Griffin

Try this...

=COUNTIFS({Template -Tasks Range 1 - Due Date}, <=TODAY(7), {Other Sheet Status Column}, @cell <> "Complete")

Using the COUNTIFS function (with the "S" on the end) allows you to include additional range/criteria sets.

"<>" means "not equal to".

SteelRed1

@Paul Newcome - sorry, I know this is an old thread. I'm trying to do something similar to Jessica but I can't get it to work.

=COUNTIFS([Due Date]:[Due Date], AND(@cell >= TODAY(), @cell <= TODAY(+7)), (Status:Status, (@cell <> "Completed")))

I can get the formula to work without the added criteria. So I know that's where the problem lies.

Any help would be great.

Paul Newcome

@SteelRed1 You have some unnecessary parenthesis.

=COUNTIFS([Due Date]:[Due Date], AND(@cell >= TODAY(), @cell <= TODAY(+7)), Status:Status, @cell <> "Completed")

SteelRed1

Duh! Thank you.

Paul Newcome

@SteelRed1 Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.