Using "IF" with "And" for multiple criteria
![Susan L.](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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!
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
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!