Excluding and Including data in a countifs formula

Options

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

Tags:

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @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
    BethWork ✭✭✭✭
    Options

    @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
    BethWork ✭✭✭✭
    Options

    @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
    BethWork ✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!