COUNTIFS formula with 3 criteria (2 of the criteria have formulas)

Options

Hi - I am trying to create a COUNTIFS formula using three criteria in the same sheet - Status (I just want to count the Open items), Impacted IT Projects (which is another dropdown of project names to select from), and Severity (this column displays a "High", "Medium", or "Low" value based on the calculated score in column Risk Exposure Score (which is calculated by multiplying the value in the Impact column by the value in the Probability column).

This is the the only formula I've been able to get to work:

=COUNTIFS(Status:Status, "Open") + COUNTIFS(Severity:Severity, "High") + COUNTIFS([Impacted IT Projects]:[Impacted IT Projects], "IT Project Name 1")

But it is building the count independently for each column referenced instead of just giving me one total count that meets all three conditions.

Is a formula like this possible? Any help would be greatly appreciated!

Best Answer

  • JamesB
    JamesB ✭✭✭✭✭✭
    edited 08/18/23 Answer ✓
    Options

    @Shari D My apologies, should have been HAS

    =COUNTIFS(Status:Status, "Open", Severity:Severity, "High", [Impacted IT Projects]:[Impacted IT Projects], HAS(@cell, "IT Project Name 1"))

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    edited 08/18/23
    Options

    @Shari D A COUNTIFS Statement is a multi criterion formula. Instead of running a COUNTIFS for each criterion, put all three in one COUNTIFS statement. This will require all 3 of the criteria to be true in a single row to be counted.

    =COUNTIFS(Status:Status, "Open",Severity:Severity, "High",[Impacted IT Projects]:[Impacted IT Projects], "IT Project Name 1")

  • Shari D
    Shari D ✭✭
    Options

    Thanks - the formula is no longer erroring, but I am getting a count of 0 which I know isn't true. Any other ideas?

  • Monique_Odom_Comcast
    Monique_Odom_Comcast ✭✭✭✭✭✭
    Options

    Hello @Shari D, sounds like you can simplify this by combining these into a single Countifs formula. Try this:

    =COUNTIFS([Status]:[Status], "Open", [Severity]:[Severity], "High", [Impacted IT Projects]:[Impacted IT Projects], "IT Project Name 1")

    If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!

    Monique Odom

    Business Process Excellence Manager

    Smartsheet Leader & Community Champion

    Pronouns: She/Her (What’s this?)

    “Take chances, make mistakes, get messy!” – Ms. Frizzle

  • Shari D
    Shari D ✭✭
    Options

    Okay, I think I see the problem now. The Impacted IT Projects column is multi-select. I think the formula is just looking for cells where the only project selected is IT Project Name 1. I want the formula to count that item if IT Project Name 1 is one of the projects selected but not the only one. Thoughts?

  • JamesB
    JamesB ✭✭✭✭✭✭
    edited 08/18/23
    Options

    @Shari D You should be able to add a CONTAINS to your formula

    =COUNTIFS(Status:Status, "Open",Severity:Severity, "High",[Impacted IT Projects]:[Impacted IT Projects], CONTAINS("IT Project Name 1",@cell))

  • Shari D
    Shari D ✭✭
    Options

    No luck, I get an invalid operation error.

  • Shari D
    Shari D ✭✭
    Options

    Success!

    =COUNTIFS(Status:Status, "Open", Severity:Severity, "High", [Impacted IT Projects]:[Impacted IT Projects], CONTAINS("IT Project Name 1", @cell))

    Thank you so much!

  • JamesB
    JamesB ✭✭✭✭✭✭
    edited 08/18/23 Answer ✓
    Options

    @Shari D My apologies, should have been HAS

    =COUNTIFS(Status:Status, "Open", Severity:Severity, "High", [Impacted IT Projects]:[Impacted IT Projects], HAS(@cell, "IT Project Name 1"))

  • Shari D
    Shari D ✭✭
    Options
  • JamesB
    JamesB ✭✭✭✭✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!