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

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")

Find more posts tagged with

Formulas

Accepted answers

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")

All comments

Ryan Sides

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

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:

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.