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?
Best Answer
-
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!
Answers
-
for the not complete just use <>"Complete". The <> means is not equal to.
-
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?
-
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")
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!