Countifs that also exclude a parameter

J.Barrow

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?

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!

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.

=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!