Google Sheet IF Countif Formula

Question

I currently have a google sheet in which I am counting if column Q has a specific value column Z returns a Y and if doesn't an N

Using this formula in column Z in sheet 1 =IF(COUNTIF(Sheet3!$A$1:$A$17,Q2),"Y", "N") Sheet 3 has the reference values

Now I have additional values which would alter this and I want to know how to include them. The new values are "Cancelled" or "Postponed".

So if the formula above returned a "Y" but column AA has either the value "Cancelled" or "Postponed" it should actually return a "N"

What should the formula look like in this case

Any help on this will be greatly appreciated

Thanks

Answer 1

Use an AND

See the documentation here

=IF(AND(COUNTIF(Sheet3!$A$1:$A$17,Q2),AA2 <> "Cancelled", AA2 <> "Postponed"),"Y", "N")

Expanded:

=IF(
    AND(
        COUNTIF(Sheet3!$A$1:$A$17,Q2),
        AA2 <> "Cancelled",
        AA2 <> "Postponed"
    ),"Y", "N")

• Note that COUNTIF(Sheet3!$A$1:$A$17,Q2) was replaced by AND(COUNTIF(Sheet3!$A$1:$A$17,Q2), AA2 <> "Cancelled", AA2 <> "Postponed")

• Since you have three criteria

  1. For Q2 to appear in the list in the other sheet
  2. For AA2 to NOT (<>) contain "Cancelled"
  3. For AA2 to NOT contain "Postponed"

  This is why you have 3 arguments in the AND formula, which can accept an unlimited number of arguments.

• So only if all conditions are fulfilled, it will resolve to TRUE = Y.