Nested COUNTIF with AND

Layla Morales

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!

JayPedde

Use "CountIfs" instead of "and"

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

Layla Morales

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!

Traci Summers

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:

Layla Morales

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. :)

Traci Summers

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.

Layla Morales

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