Trouble using countifs referencing another sheet and adding either "NOT" or an "OR"

Patrick Vermaas

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)

Heather Duff

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