Set up COUNTIFS with muliple "<>"
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.
Best Answer
-
@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, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
Answers
-
@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, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
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.
-
No it doesn't. As Darren stated, the syntax is incorrect.
-
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.
-
@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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I go it figured out. Came to me while I was at the gym. Thanks.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!