Countifs - Need an OR Statement not an AND statement

Jeremy Norman

I need to set a Countifs formula that is an OR statement not an AND statement.

Basically I need to count Region Column if Experience is less than 2 in Two different columns. My Formula works but only if BOTH - RN Experience and Medic Experience is less than 2 years. I need it to count Region Column if One or the Other Column is less than 2 years. Below is my formula:

=COUNTIFS(Region:Region, "PacWest", [Paramedic- Years Experience ]:[Paramedic- Years Experience ], <2, [RN - Years Experience]:[RN - Years Experience], <2)

Any Thoughts?

Antonio Figueroa

You could use a helper column and use this formulas.

=IF(OR([Paramedic- Years Experience]@row < 2, [RN- Years Experience]@row < 2), 1, 0)

This column is labeled Column 28. Then add another column and use the following formula

=SUMIF(Region1:Region10, "PacWest", [Column28]1:[Column28]10)

So if Paramedic Years Experience is less the 2 or Rn-Years Experience is less than 2 it will return a 1

if not it will return a 0. The the second formula will sum if it is "PacWest"

Antonio Figueroa

You could use a helper column and use this formulas.

=IF(OR([Paramedic- Years Experience]@row < 2, [RN- Years Experience]@row < 2), 1, 0)

This column is labeled Column 28. Then add another column and use the following formula

=SUMIF(Region1:Region10, "PacWest", [Column28]1:[Column28]10)

So if Paramedic Years Experience is less the 2 or Rn-Years Experience is less than 2 it will return a 1

if not it will return a 0. The the second formula will sum if it is "PacWest"

Jeremy Norman

Thank you for your help and advice. Truly appreciated.