COUNTIFS, multiple selection drop down

Btrombler

Hi All,

Ive been looking through what feels like hundreds of posts trying to find a solution for this. I'm looking for a formula to count the number of times TWO criteria (or more, for future reference) have been selected from a drop-down menu.

Current: =COUNTIFS(Location:Location, HAS(@cell = "Mission Bay" + "Remote"))

Options in the drop down include: Mission Bay, Parnassus, Oakland, Remote. I can have it tally those with one option selected, but I want it to count when a distinct pair of two have been selected (i.e. both Mission Bay AND Remote). Help! I thought this would be fairly straightforward, but I've been struggling.

KPH

Hi @Btrombler

You could use

=COUNTIFS(Location:Location, HAS(@cell, "Mission Bay"), Location:Location, HAS(@cell, "Remote"))

or

=COUNTIFS(Location:Location, AND(HAS(@cell, "Mission Bay"), HAS(@cell, "Remote")))

Either will do what you want. The second is a little more complicated (it has an AND and more commas to get in the right place) but easier to extend if you want to add more options.

Explanation of example 1

COUNTIFS allow multiple range and criteria pairs, so in the first example I just say Location has Mission Bay included and Location has Remote included. You can use this to look for things in multiple columns. For example, Location could be Season, like this:

=COUNTIFS(Location:Location, HAS(@cell, "Mission Bay"), Season:Season, HAS(@cell, "Spring"))

Explanation of example 2

The AND function lets you include two HAS functions, so in the second example I say Location has Mission Bay included and has Remove included. This is easier to extend to other options in the same column (as you mention you might). For example:

=COUNTIFS(Location:Location, AND(HAS(@cell, "Mission Bay"), HAS(@cell, "Remote"), HAS(@cell, "Oakland")))

KPH

Hi @Btrombler

You could use

=COUNTIFS(Location:Location, HAS(@cell, "Mission Bay"), Location:Location, HAS(@cell, "Remote"))

or

=COUNTIFS(Location:Location, AND(HAS(@cell, "Mission Bay"), HAS(@cell, "Remote")))

Either will do what you want. The second is a little more complicated (it has an AND and more commas to get in the right place) but easier to extend if you want to add more options.

Explanation of example 1

COUNTIFS allow multiple range and criteria pairs, so in the first example I just say Location has Mission Bay included and Location has Remote included. You can use this to look for things in multiple columns. For example, Location could be Season, like this:

=COUNTIFS(Location:Location, HAS(@cell, "Mission Bay"), Season:Season, HAS(@cell, "Spring"))

Explanation of example 2

The AND function lets you include two HAS functions, so in the second example I say Location has Mission Bay included and has Remove included. This is easier to extend to other options in the same column (as you mention you might). For example:

=COUNTIFS(Location:Location, AND(HAS(@cell, "Mission Bay"), HAS(@cell, "Remote"), HAS(@cell, "Oakland")))

Btrombler

YOU are amazing, thank you for your quick help!!

Btrombler

Out of curiosity, how might I adjust the 2nd formula to count how many have "any location" (Mission Bay, Oakland, Parnassus) selected in addition to "Remote" ? But not count those that have only remote selected, must have 2 answers selected, one of which includes remote.

KPH

I'd use

=COUNTIFS(Location:Location, OR(HAS(@cell, "Mission Bay"), HAS(@cell, "Oakland"), HAS(@cell, "Parnassus")), Location:Location, HAS(@cell, "Remote"))

The first range/criteria checks the row has at least one of the places:

Location:Location, OR(HAS(@cell, "Mission Bay"), HAS(@cell, "Oakland"), HAS(@cell, "Parnassus"))

Then the second range/criteria checks it also has "Remote" checked

Location:Location, HAS(@cell, "Remote")

Or you could do it in one, like this, but I think this is harder to read and edit:

=COUNTIFS(Location:Location, AND(HAS(@cell, "Remote"), OR(HAS(@cell, "Mission Bay"), HAS(@cell, "Oakland"), HAS(@cell, "Parnassus"))))

Btrombler

You have made my morning, thank you!

KPH

Yay! 😍