Another COUNTIFS question with multiple criteria **Answered**

Jake_12

Can someone help me adjust below equation to exclude "Cancelled" as well as "Complete". It already works to exclude "Complete". I've tried multiple ways but have failed so far.

=COUNTIFS({HR Info Systems COE Tracker Range 4}, [Metrics 2]@row, {HR Info Systems COE Tracker Status}, <>"Complete")

Jake_12

@heyjay Your solution works if I use AND instead of OR - thank you!!

=COUNTIFS(

{HR Info Systems COE Tracker Range 4}, [Metrics 2]@row,

{HR Info Systems COE Tracker Status}, AND(@cell <>"Complete", @cell <>"Cancelled"))

@DKazatsky2 Your solution works - thanks so much!!

=COUNTIFS({HR Info Systems COE Tracker Range 4}, [Metrics 2]@row, {HR Info Systems COE Tracker Status}, <>"Complete", {HR Info Systems COE Tracker Status}, <>"Cancelled")

heyjay

Can you try this?

=COUNTIFS(
{HR Info Systems COE Tracker Range 4}, [Metrics 2]@row, 
  OR(
  {HR Info Systems COE Tracker Status}, <>"Complete",
  {HR Info Systems COE Tracker Status}, <>"Cancelled",)
)

Jake_12

https://community.smartsheet.com/discussion/comment/447717#Comment_447717

@heyjay Thanks for your response - I tried and I get an unparseable error.

heyjay

My bad, has extra comma at the end.

=COUNTIFS(
{HR Info Systems COE Tracker Range 4}, [Metrics 2]@row,   
OR(  
{HR Info Systems COE Tracker Status}, <>"Complete",  
{HR Info Systems COE Tracker Status}, <>"Cancelled"
))

Jake_12

https://community.smartsheet.com/discussion/comment/447723#Comment_447723

I just noticed that when you replied - it shows #INVALID DATA TYPE now :(

heyjay

=COUNTIFS({HR Info Systems COE Tracker Range 4}, [Metrics 2]@row,
{HR Info Systems COE Tracker Status}, <>"Complete") + 

COUNTIFS({HR Info Systems COE Tracker Range 4}, [Metrics 2]@row,
{HR Info Systems COE Tracker Status}, <>"Cancelled")

How bout we do 2 counts for separate criteria then add them?

Jake_12

https://community.smartsheet.com/discussion/comment/447725#Comment_447725

No error - but it is over counting. For example it is counting a status of 'New" in both cases. Would it be best for me to just use this method for all the in-scope status's, rather than trying to exclude the 2 out of scope?

heyjay

=COUNTIFS(
{HR Info Systems COE Tracker Range 4}, [Metrics 2]@row,
{HR Info Systems COE Tracker Status}, OR(@cell <>"Complete",@cell <>"Cancelled"))

DKazatsky2

Hi @Jake_12,

Try this.

=COUNTIFS({HR Info Systems COE Tracker Range 4}, [Metrics 2]@row, {HR Info Systems COE Tracker Status}, <>"Complete", {HR Info Systems COE Tracker Status}, <>"Cancelled")

Hope this helps,

Dave

Jake_12

@heyjay Your solution works if I use AND instead of OR - thank you!!

=COUNTIFS(

{HR Info Systems COE Tracker Range 4}, [Metrics 2]@row,

{HR Info Systems COE Tracker Status}, AND(@cell <>"Complete", @cell <>"Cancelled"))

@DKazatsky2 Your solution works - thanks so much!!

=COUNTIFS({HR Info Systems COE Tracker Range 4}, [Metrics 2]@row, {HR Info Systems COE Tracker Status}, <>"Complete", {HR Info Systems COE Tracker Status}, <>"Cancelled")

Jake_12

FYI I'm trying to figure out how to get this changed to a question, so I can mark answered