Using "IF" with "And" for multiple criteria

Susan L.

Hello,

I'm struggling with the syntax for a formula-I've seen examples within the community and can't quite get them to work properly for me.

I would like to count the following:

Count values where the following statement is true:

column 1 =ABC, or, DEF, or, GHI AND where column 2 =123, or, 456

I've tried =COUNTIF([Column 1]@row, OR(@cell = "ABC", @cell = "DEF", @cell = "GHI")) AND([Column 2]@row, OR(@cell = "123", @cell = "456"))

I'm 100% confident that this is entirely wrong, but, I don't quite have the skills to match the formula functions with the words I'm using to describe my desired outcome. I'm wondering if I need to use a "CONTAINS" ? AND(CONTAINS("123"@cell))?

With thanks!

Jason Tarpinian

COUNTIFS doesn't work well with AND/OR, as the AND/OR function returns boolean TRUE/FALSE. I'd suggest making it simple with a helper column built as below, then just SUM/COUNT the helper column. If your solution does require using the COUNTIFS, you'd have to have just as many COUNTIFS() + COUNTIFS + ... etc as you do possible outcomes.

=IF(AND(OR([Column 1]@row = "ABC", [Column 1]@row = "DEF", [Column 1]@row = "GHI"), OR([Column 2]@row = 123, [Column 2]@row = 456)), 1, 0)