In Excel, how do I count the number of rows in a table that pass a filter with AND and OR logic?

Question

In Excel, I have data like this:

I want to put together a formula that counts the rows where Col1 is "a" or "b" and Col2 is "c" or "d". I highlighted the rows that should be included. The result should be 4.

I've tried a few options:

(1) Using COUNTIFS with multiple possible values. This returns 2 instead of 4. I don't know why

=SUM(COUNTIFS(A2:A7,{"a","b"},B2:B7,{"c","d"}))

(2) USING COUNTIFS which each possible combination of filters. This works, but the formula is long. And the size of it will explode if I add more filters with more possible values

=COUNTIFS(A2:A7,"a",B2:B7,"c")+COUNTIFS(A2:A7,"a",B2:B7,"d")+COUNTIFS(A2:A7,"b",B2:B7,"c")+COUNTIFS(A2:A7,"b",B2:B7,"d")

(3) ChatGPT answer. This returns 0 instead of 4. I got this from ChatGPT. It gave me a few different formulas that would return either 0 or 1.

=SUMIFS(C2:C7, A2:A7, {"a","b","c","d","e","f","g"}, B2:B7, {"b","c","d","e","f","g","h"})

I'd also like a formula that sums column "N" for rows that pass this filtering. Can someone give me a hand with this?

Here's the data as text

Col1    Col2    N
b   c   1
a   c   2
a   d   3
b   d   4
b   e   5
c   e   6

Answer 1

When Using arrays in COUNTIFS if the arrays are the same direction (Horizontal vs Vertical) it will do them in pairs.

So:

=SUM(COUNTIFS(A2:A7,{"a","b"},B2:B7,{"c","d"}))

Is like doing:

=COUNTIFS(A2:A7,"a",B2:B7,"c")+COUNTIFS(A2:A7,"b",B2:B7,"d")

To get all variations, make one vertical and the other horizontal

=SUM(COUNTIFS(A2:A7,{"a","b"},B2:B7,{"c";"d"}))

Which will now do all four variations:

Now the Caveat: This only works with TWO arrays. Trying to add a third will put it in sync with one of the other two. So to do OR with more than two arrays will take a different formula.

Answer 2

Use a formula like this:

=SUMPRODUCT(($A$2:$A$7="a")+($A$2:$A$7="b"),($B$2:$B$7="c")+($B$2:$B$7="d"))  

This way you can use as many conditions as you need.
But SUMPRODUCT is slower than COUNTIFS.

Answer 3

I've tested many combinations but these two works without any failure, so I would like to recommend these two:

=SUM(IF((A2:A7={"a","b"})+(B2:B7={"c","d"}),1,0))

=COUNTIF($A$2:$A$7,"=a")+COUNTIF($A$2:$A$7,"=b")+COUNTIF($B$2:$B$7,"=c")+COUNTIF($B$2:$B$7,"=d")

• Adjust cell references & criteria as needed.