CountIF for Overdue Tasks that are not Complete

I'm trying to count overdue tasks in Sheet Summary, but only for Tasks that are not marked as Complete. (Marked In Progress or Not Started).

=COUNTIF(Status:Status, OR(Status:Status = "In Progress", Status:Status = "Not Started"), [End Date]:[End Date], <TODAY())

It returns the #incorrect argument set error. Any thoughts on what's wrong?

Find more posts tagged with

Accepted answers

Todd M Keller

Give this a try instead......

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

All comments

Todd M Keller

Give this a try instead......

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

Adelina

Thanks! That works, I also missed an S in COUNTIFS!

Helena P.

HI all, I am struggling to the the right number as some status cells are blank. How should I tell the formula to include as well cells which are blank? I tried with OR but got error Unpareasble.

=COUNTIFS({Wave 2 WRICEFU Design & Build Tracker Range 2}, <>"12 Descoped", {Wave 2 WRICEFU Design & Build Tracker Range 4}, OR(@Cell ="", @cell <>"Approved"), {Wave 2 WRICEFU Design & Build Tracker Range 11}, <TODAY())

Genevieve P.

Hi @Helena P.

The reason you're seeing unparseable is because the @cell function needs to be all lower case. In the OR function you have a capital C in the @Cell, which will mean it won't work.

That said, excluding cells that say "Approved" will then include cells that are blank (since they do not say "Approved").

Try it again:

=COUNTIFS({Wave 2 WRICEFU Design & Build Tracker Range 2}, <>"12 Descoped", {Wave 2 WRICEFU Design & Build Tracker Range 4}, <>"Approved", {Wave 2 WRICEFU Design & Build Tracker Range 11}, <TODAY())

Cheers,
Genevieve

Helena P.

HI @Genevieve P.

thank you :)

I tried but received an error, inv ref

Genevieve P.

Hi @Helena P.

That looks to be the same formula as before - have you removed out the @Cell and = "" reference?

=COUNTIFS({Wave 2 WRICEFU Design & Build Tracker Range 2}, <> "12 Descoped", {Wave 2 WRICEFU Design & Build Tracker Range 4}, @cell <>"Approved", {Wave 2 WRICEFU Design & Build Tracker Range 11}, <TODAY())

An Invalid Reference error means that one of the references is potentially set up incorrectly. Double check that each of these are pointing to the right column:

{Wave 2 WRICEFU Design & Build Tracker Range 2}
{Wave 2 WRICEFU Design & Build Tracker Range 4}
{Wave 2 WRICEFU Design & Build Tracker Range 11}

Helena P.

HI, so I did check it and still nothing. I slightly changed the formula to count items with any of those statuses:

submitted

needs re-work

or blank

and received an error unparseable

=COUNTIFS([Overall WRICEFU Status]:[Overall WRICEFU Status], <>"12 Descoped", [Functional Approver(PDL) approval]:[Functional Approver(PDL) approval], OR(@cell = "Submitted", @cell = "Needs Re-Work", @cell = ""), [FS PDL + LPO + Int. Lead Approval Date]:[FS PDL + LPO + Int. Lead Approval Date], <TODAY()))

Genevieve P.

Hi @Helena P.

In this instance, the OR statement is not being closed off OR( - - - )

=COUNTIFS([Overall WRICEFU Status]:[Overall WRICEFU Status], <>"12 Descoped", [Functional Approver(PDL) approval]:[Functional Approver(PDL) approval], OR(@cell = "Submitted", @cell = "Needs Re-Work", @cell = ""), [FS PDL + LPO + Int. Lead Approval Date]:[FS PDL + LPO + Int. Lead Approval Date], <TODAY())

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.