Trying to include two bits of criteria from the same column in a COUNTIFS formula

BethWork

I have used a variation of this formula so I can compile the total amount of stores in two particular states. I was able to exclude both NY & MA in a similar formula using the <> before {ALL STORE Rollout Schedule-IT-1547-EG Range 5}, but now that I ONLY want to include MA & NY I'm having trouble. I'm able to do one state or the other, but when I have the formula outlined like it is below then it gives me a result of zero which I know to be incorrect.

=COUNTIFS({ALL STORE Rollout Schedule-IT-1547-EG Range 1}, "Store Name", {ALL STORE Rollout Schedule-IT-1547-EG Range 4}, "Open", {ALL STORE Rollout Schedule-IT-1547-EG Range 5}, "MA", {ALL STORE Rollout Schedule-IT-1547-EG Range 5}, "NY")

Ryan Sides

@BethWork Hi Beth! In your formula above, the COUNTIFS is looking for all scenarios to be true in order to count it. So you're saying Range 5 should equal MA and it should also equal NY. IT can't equal both, so it finds zero instances of that.

You'll want to use the OR operator. So just use one reference to your Range 5, then...

OR(@cell = "MA", @cell = "NY")

=COUNTIFS({ALL STORE Rollout Schedule-IT-1547-EG Range 1}, "Store Name", {ALL STORE Rollout Schedule-IT-1547-EG Range 4}, "Open", {ALL STORE Rollout Schedule-IT-1547-EG Range 5}, OR(@cell = "MA", @cell = "NY"))

Ryan Sides

@BethWork Hi Beth! In your formula above, the COUNTIFS is looking for all scenarios to be true in order to count it. So you're saying Range 5 should equal MA and it should also equal NY. IT can't equal both, so it finds zero instances of that.

You'll want to use the OR operator. So just use one reference to your Range 5, then...

OR(@cell = "MA", @cell = "NY")

=COUNTIFS({ALL STORE Rollout Schedule-IT-1547-EG Range 1}, "Store Name", {ALL STORE Rollout Schedule-IT-1547-EG Range 4}, "Open", {ALL STORE Rollout Schedule-IT-1547-EG Range 5}, OR(@cell = "MA", @cell = "NY"))

Heather_Verde

If it's not a contact cell, which it looks like it's not per what you've provided, you probably need to use the AND and CONTAINS within your formula. I would suggest:

=COUNTIFS({ALL STORE Rollout Schedule-IT-1547-EG Range 1}, "Store Name", {ALL STORE Rollout Schedule-IT-1547-EG Range 4}, "Open", {ALL STORE Rollout Schedule-IT-1547-EG Range 5}, AND(CONTAINS("MA",@cell), CONTAINS("NY",@cell)))

BethWork

@Ryan Sides Ah! I tried and OR statement earlier and clearly had my syntax completely wrong because I could get it to work. Yours worked perfectly. Thank you!