Using "IF" with "And" for multiple criteria
Hello,
I'm struggling with the syntax for a formulaI'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!
Answers

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
Categories
Check out the Formula Handbook template!