COUNTIF using 2 criteria

deb_63_hydracor

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!

Mike TV

@deb_63_hydracor

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")

Mike TV

First formula should be something like:

=COUNTIFS([Experiencia Rounding Status]:[Experiencia Rounding Status], "Operational", [EHR Integration Status]:[EHR Integration Status], <>"Operational")

deb_63_hydracor

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!

Genevieve P.

Hi @deb_63_hydracor

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. 🙂

deb_63_hydracor

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!

Genevieve P.

Hi @deb_63_hydracor

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.

deb_63_hydracor

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!

Genevieve P.

Hi @deb_63_hydracor

Are either of the columns multi-select? Or are they single-select dropdowns?

deb_63_hydracor

They are both single-select.

Genevieve P.

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?