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!

Tags:

Answers

  • Mike TV
    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
    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
    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.
    Genevieve P. Employee Admin
    edited 01/03/23

    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
    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.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    Hi @deb_63_hydracor

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

  • deb_63_hydracor
    deb_63_hydracor ✭✭✭✭✭✭

    They are both single-select.

  • Genevieve P.
    Genevieve P. Employee Admin

    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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!