Countifs that also exclude a parameter

So I am trying to do a countifs report that counts if something has a name, a ranking and then to not count if the status is complete.

I have it working to count if it has the name and ranking, but when I try to get it to not count them if they are marked complete it does not work.

The working formula:

=COUNTIFS({Third Party Inspection Tracking Range 2}, $[Primary Column]@row, {Third Party Inspection Tracking Range 1}, [#1 - Non Compliance]$1)

But does not exclude the completed tasks, so I tried this formula to not count the completed:

=COUNTIFS({Third Party Inspection Tracking Range 2}, $[Primary Column]@row, {Third Party Inspection Tracking Range 1}, [#1 - Non Compliance]$1, NOT(FIND ("Complete", LOWER({Third Party Inspection Tracking Range 3}))>0))

But that is not working any suggestions from the braintrust of the community?

Find more posts tagged with

Accepted answers

J.Barrow

@Mike TV

Thanks that ended up working I just needed to get the references correct.

=COUNTIFS({Third Party Inspection Tracking Range 1}, [#1 - Non Compliance]$13, {Third Party Inspection Tracking Range 2}, $[Primary Column]@row, {Third Party Inspection Tracking Range 3}, <>"Complete")

Thanks for taking the time to help!

All comments

Mike TV

@J.Barrow

for the not complete just use <>"Complete". The <> means is not equal to.

J.Barrow

@Mike TV

I tried it this way and still did not work.

=COUNTIFS({Third Party Inspection Tracking Range 2}, $[Primary Column]@row, {Third Party Inspection Tracking Range 1}, [#1 - Non Compliance]$1, NOT(FIND("Complete", LOWER({Third Party Inspection Tracking Range 3}))))

and this way

=COUNTIFS({Third Party Inspection Tracking Range 2}, $[Primary Column]@row, {Third Party Inspection Tracking Range 1}, [#1 - Non Compliance]$1, NOT(FIND(LOWER({Third Party Inspection Tracking Range 3}"Complete"))))

Any More Suggestions?

Mike TV

@J.Barrow

No you didn't try what I said. You'll put in your cross-sheet reference range and then a comma and then <> "Complete". So something like this:

=COUNTIFS({Third Party Inspection Tracking Range 2}, $[Primary Column]@row, {Third Party Inspection Tracking Range 1}, [#1 - Non Compliance]$1, {crosssheet reference}, <>"Complete")

J.Barrow

@Mike TV

Thanks that ended up working I just needed to get the references correct.

Thanks for taking the time 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.