Set up COUNTIFS with muliple "<>"

french2s

I am trying to make this work. I want the formula to count all the store numbers except for the ones where the Contract Status is not equal to several options in the Contract Status column. Not sure where I am going wrong.

=COUNTIFS({Circle K Summary Data Store #}, {Circle K Summary Data Contract Status}, <>"Submitted to CK", {Circle K Summary Data Contract Status}, <>"PSA Sent to Buyer", {Circle K Summary Data Contract Status}, <>"LOI Accepted", {Circle K Summary Data Contract Status}, <>"Drafting PSA", {Circle K Summary Data Contract Status}, <>"Closing", {Circle K Summary Data Contract Status}, <>"CK Submitted for Approval", {Circle K Summary Data Contract Status}, <>"Closed")

As a side note: If I use =COUNT({Circle K Summary Data Store #})- I do get the total number of rows of 272.

Thanks.

Darren Mullen

@french2s You have the range {Circle K Summary Data Store #} at the beginning followed by another range, {Circle K Summary Data Contract Status}. The Syntax for Countsifs is COUNTIFS(range 1, criterion1, range2, criterion 2....)

Darren Mullen

@french2s You have the range {Circle K Summary Data Store #} at the beginning followed by another range, {Circle K Summary Data Contract Status}. The Syntax for Countsifs is COUNTIFS(range 1, criterion1, range2, criterion 2....)

french2s

It works on another calculation. It should work here.

However, since it does not:

I need to count the number of Store # (rows) that do not meet a certain contract status criteria, or count only the ones that meet a criteria.

Mike TV

https://community.smartsheet.com/discussion/comment/345595#Comment_345595

No it doesn't. As Darren stated, the syntax is incorrect.

french2s

Mike - that was not helpful. I am looking for help. And yes this works: =COUNTIFS({ Summary Data Region}, [Primary Column]@row, {Summary Data Contract Status}, <>"Reviewing/Negotiating")

Regardless, what I stated above. I need it for a major presentation and your snarkiness is not needed. Have a nice day.

Paul Newcome

@french2s The appropriate syntax for a COUNTIFS is

range, criteria, range, criteria................

The formula in your original post is

range, range, criteria, range....................

It looks like you are wanting to evaluate the Status range, so removing that first range and proceeding comma should do the trick for you.

french2s

I go it figured out. Came to me while I was at the gym. Thanks.