Nested COUNTIF with AND

I am trying to use a nested COUNTIF formula with AND function.

Column X - check if value is "1"

Column Y - check if value is "2"

Column Z - check if value is "3"

I only want to see the count where all three statements are true. Help!

Tags:

Answers

  • JayPedde
    JayPedde ✭✭✭

    Use "CountIfs" instead of "and"

    =COUNTIFS([Column X]: [Column X], 1, [Column Y]: [Column Y], 2, [Column Z]: [Column Z], 3)

  • Thanx Jay. This did not work - let me give you the actual column and value names:

    Column 1 = PgM/PjM Execution Value = Layla Morales

    Column 2 = Categories Value = Merger Integration

    Column 3 = Phase Value = In Progress

    Column 4 = Phase Value = Discovery

    Looking for a count of when all values are true :) Thanx for your help!

  • Hi Layla,

    I'm procrastinating while trying to figure out an if/and/or nesting (or even if I can do it) and came across your question. JayPedde is correct that you only need a countifs to accomplish what you are looking for. I built a sample below. You do have two columns named "Phase Value" in your scenario, so I just updated one to "Phase Value 2". Formula is:

    =COUNTIFS([PgM/PjM Execution Value]:[PgM/PjM Execution Value], ="Layla Morales", [Categories Value]:[Categories Value], ="Merger Integration", [Phase Value]:[Phase Value], ="In Progress", [Phase Value 2]:[Phase Value 2], ="Discovery")

    What that results in:


  • I think that we have identified the issue. For the first two values, there are two separate columns. For the last two values, I'm asking the formula to look at the same column for two different values. I believe that this is the issue here. Sounds like, with COUNTIFS, you are limited to look for one value per column. Can anyone confirm? If so, I will need to make changes to my base sheet values. :)

  • Hi Layla,

    Try this: =COUNTIFS([PgM/PjM Execution Value]:[PgM/PjM Execution Value], ="Layla Morales", [Categories Value]:[Categories Value], ="Merger Integration", [Phase Value]:[Phase Value], OR(@cell = "In Progress", @cell = "Discovery"))

    It combines the countifs with an or for the Phase Value column. Sheet view is below.


  • The formula above didn't work, but this variation finally did! =COUNTIFS({PgM/PjM Execution}, ="Layla Morales", {Categories}, ="Merger Integration", {Phase}, OR(@cell = "In Progress", @cell = "Discovery"))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!