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!

Find more posts tagged with

Formulas

Accepted answers

All comments

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.