COUNTIF using 2 criteria
Hi,
I'm trying to count the number of accounts using Experiencia with following criteria:
Rounding Status is Operational AND EHR Status is NOT Operational. I'm getting an unparseable error with this formula:
=COUNTIF([Experiencia Rounding Status]@row = "Operational", [EHR Integration Status]@row <> "Operational")
Next, I want to count the number of Plus accounts but am also getting an unparseable error.
=COUNTIF([EHR Integration Status]@row= "Operational")
I'm not using cross-sheet references in either formula.
Thanks for your help!
Answers
-
For the first problem, you need to use COUNTIFS (with an S at the end) so that it allows for multiple criteria.
For the second problem, you need to specify a range which looks like it should be [EHR Integration Status]:[EHR Integration Status]. So it would look something like this:
=COUNTIF([EHR Integration Status]:[EHR Integration Status], "Operational")
-
First formula should be something like:
=COUNTIFS([Experiencia Rounding Status]:[Experiencia Rounding Status], "Operational", [EHR Integration Status]:[EHR Integration Status], <>"Operational")
-
The solution you provided for my second formula worked. Thank you!
For my first formula, adding an "s" did not help. Any other suggestions?
Thanks!
-
Did you try Mike's second comment for your first formula? Other than the S, you'll need to add commas between your [ranges] and "criteria". The second comment with the spelled out formula should do the trick. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi,
I don't get an error anymore but the formula is not returning the correct value. This is what I have now:
=COUNTIFS([Experiencia Rounding Status]:[Experiencia Rounding Status], "Operational", [EHR Integration Status]:[EHR Integration Status], <>"Operational")
I feel like formulas often don't like "<>" Do you think that could be the issue?
Thanks!
-
Personally I've <> for "does not equal" to be reliable! Your formula is built correctly based on the structure so let's take a look at the logic.
The same row will need to have your two criteria:
- "Operational" listed in the [Experiencia Rounding Status] column
- Anything other than "Operational" in the [EHR Integration Status] column
Is that what you're looking to count? Note that anything other than "Operational" can mean blank cells as well. You can test the "count" by adding a filter to your sheet to see just the rows where the two criteria are met.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi Genevieve,
Yes, what you stated above is what I'm looking to count. The correct figure is 234 (based on doing a filter) but I'm getting 2! There are so many options in the drop-down in these columns that I'm trying to avoid having to include them all in my formula when "Operational" is the only option that I want to exclude.
Thanks!
-
Are either of the columns multi-select? Or are they single-select dropdowns?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
They are both single-select.
-
Are you able to post a screen capture of the sheet, but block out sensitive data? Is there any chance that "Operational" is spelled differently in the dropdown?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!