Trouble using countifs referencing another sheet and adding either "NOT" or an "OR"
I am trying to write something that will do multiple countifs and either include a range of ORs or allow me to exclude rows that have a particular response in a given column.
=COUNTIFS({NEOP Tracking Range 2}, [Column5]12, {NEOP Tracking Range 4}, [Column5]@row)
This works great.
Now I need to only see employees that have a specific status.
=COUNTIFS({NEOP Tracking Range 2}, [Column5]12, {NEOP Tracking Range 4}, [Column5]@row) NOT({NEOP Tracking Range 1},[Column8]7, {NEOP Tracking Range 1}, [Column8]8
Comes back as #unparseable
and when I try to do a range of answers instead of using NOT
=COUNTIFS({NEOP Tracking Range 2}, [Column5]12, {NEOP Tracking Range 4}, [Column5]@row, {NEOP Tracking Range 1}, [Column5]14:[Column5]18)
I get "0" instead of 3 so I am guessing the range isn't coming across as this OR this OR this.
So I tried this where I listed everything out using OR and it comes back as #unparseable
=COUNTIFS({NEOP Tracking Range 2}, [Column5]12, {NEOP Tracking Range 4}, [Column5]@row, {NEOP Tracking Range 1}, [Column5]14 OR([Column5]18 OR([Column5]15 OR([Column5]16 OR([Column5]17)
Answers
-
Hi Patrick,
Try one of these:
=COUNTIFS({NEOP Tracking Range 2}, [Column5]12, {NEOP Tracking Range 4}, [Column5]@row, {NEOP Tracking Range 1}, OR(@cell=[Column5], @cell=[Column5]18, @cell=[Column5]15,@cell=[Column5]16 @cell=[Column5]17))
=COUNTIFS({NEOP Tracking Range 2}, [Column5]12, {NEOP Tracking Range 4}, [Column5]@row, {NEOP Tracking Range 1}, NOT([Column8]7), {NEOP Tracking Range 1}, NOT([Column8]8))
Let me know if one of them works!
Best,
Heather
-
Thank you so much for your quick response. Unfortunately they came back as #invalid operation. I can't figure it out for the life of me either.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.6K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!