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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.9K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.8K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!