Using "IF" with "And" for multiple criteria



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

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!