Excluding and Including data in a countifs formula

I need the total number of that meets the following criteria - Banner Name, Open, and not MA or NY. I am able to write the countifs to include the specific banner name and whether the status of that site is open, but I can't figure out how to exclude certain states or include certain states. This is what I tried below based off another community post, but it didn't work for me.

=COUNTIFS({ALL STORE Rollout Schedule-IT-1547-EG Range 1}, "Banner Name", {ALL STORE Rollout Schedule-IT-1547-EG Range 4}, "Open", NOT(OR({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

All comments

Mike TV

@BethWork

Try this:

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

<> means not equal to

BethWork

@Mike TV I'm having trouble getting this to work. It's definitely on the right track of excluding those two states from the formula like I need, but when I apply it in my sheet it gives me an INVALID error. Any ideas?

BethWork

@Mike TV I figured it out! There were two missing commas in the formula you gave me. Adding a comma before the <> worked perfectly.

BethWork

@Andrée Starå Are you able to help me with something else in regards to this formula?

I first wanted to exclude NY and MA from the formula which I did and it works as expected returning the total results. Now I want to reference the same exact columns and ONLY include MA & NY. However, I use the COUNIFS formula below and it returns zero results which is in accurate. I can remove the reference to either MA or NY and it works, but not when I include both.

=COUNTIFS({ALL STORE Rollout Schedule-IT-1547-EG Range 1}, "Cumberland Farms", {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")

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.