Countifs with OR

[Turbo68]

I have tried to get this done, but for some reason, my CountIfs will not work with OR criteria in the formula. The first 2 will count them fine but line 3, which I thought I had the correct code, doesn't!


=IF(COUNTIFS('MERAKI CSV DATA'!A:A,A208,'MERAKI CSV DATA'!D:D,"MX6*",'MERAKI CSV DATA'!O:O,"=*SEC*")=1,"YES","NO") - WORKS
=IF(COUNTIFS('MERAKI CSV DATA'!A:A,A208,'MERAKI CSV DATA'!D:D,"MX6*",'MERAKI CSV DATA'!O:O,"=*MIN*")=1,"YES","NO") - WORKS

=IF(COUNTIFS('MERAKI CSV DATA'!A:A,A208,'MERAKI CSV DATA'!D:D,"MX6*",'MERAKI CSV DATA'!O:O,{"=*SEC*","=*MIN*"})=1,"YES","NO") - FAILS, will not find either text when i use this.


Any help is greatly appreciated!

Mike

 

[Eric W]

Try:

=IF(SUM(COUNTIFS('MERAKI CSV DATA'!A:A,A208,'MERAKI CSV DATA'!D:D,"MX6*",'MERAKI CSV DATA'!O:O,{"*SEC*","*MIN*"}))=1,"YES","NO")

 

[jasonb75]

You missed the OR from the formula

=IF(OR(COUNTIFS('MERAKI CSV DATA'!A:A,A208,'MERAKI CSV DATA'!D:D,"MX6*",'MERAKI CSV DATA'!O:O,{"=*SEC*","=*MIN*"})=1),"YES","NO")

edit:-

Note that the results of my suggestion will differ from Eric's.

Both will return "YES" if there is 1 *SEC* or 1 *MIN*, if there is 1 of each then Eric's will return "NO", mine will return "YES".

 

[Eric W]

Both will return "YES" if there is 1 *SEC* or 1 *MIN*, if there is 1 of each then Eric's will return "NO", mine will return "YES".

Excellent point. Here's another slight variation:

=IF(SUM(COUNTIFS('MERAKI CSV DATA'!A:A,A208,'MERAKI CSV DATA'!D:D,"MX6*",'MERAKI CSV DATA'!O:O,{"*SEC*","*MIN*"}))>=1,"YES","NO")


This returns "YES" if there is 1 OR MORE of either value.

 

[Turbo68]

Thanks, Eric and Jason! I am away from work but will try these out Monday and get back to you. Pretty sure they will work .

Mike

 

[Turbo68]

Yep, thanks guys, works perfectly!

 

[Eric W]

Glad we could help! ??