Excel Countifs/Sumproduct with mutil Or statement

[dhaiftmc]

I have a random letter in column A & B. For example,

__ | A B
_1 | A A
_2 | A B
_3 | E C
_4 | E D
_5 | B E
_6 | B F
_7 | F G
_8 | F H
_9 | C A
10 | C B
11 | G C
12 | G D
13 | D E
14 | D F
15 | H G
16 | H H

I want to count where column A = A/B/C/D AND column B = E/F/G/H
I try it work with this formula,

=SUMPRODUCT(((A1:A100="A")+(A1:A100="B")+(A1:A100="C")+(A1:A100="D"))*((B1:B100="E")+(B1:B100="F")+(B1:B100="G")+(B1:B100="H")))

But it's too complicated.
I try to simplify the formula with this one, it doesn't work.

=SUMPRODUCT(((A1:A100={"A","B","C","D"}))*((B1:B100={"E","F","G","H"})))

Is there other simple way to do this?

 

[Becky]

COUNTIFS is a nice formula for this - it basically counts the number of cells in which certain criteria are met. This should work for you:

=SUM(COUNTIFS(A:A,{"A";"B";"C";"D"},B:B,{"E","F","G","H"}))

Note that the separators within the first array are semi-colons not commas. Won't work without that!

 

[dhaiftmc]

Thank you! It's work for me.
May I ask why A:A,{"A";"B";"C";"D"} use semicolon
B:B,{"E","F","G","H"} use comma ?
If there have column C D E... , it still use comma ?

 

[Becky]

If it used commas instead of semi-colons, like this:

=SUM(COUNTIFS(A:A,{"A","B","C","D"},B:B,{"E","F","G","H"}))

Then it matches up the criteria in the arrays in order - ie it would only count instances where there was an A in the first column and an E in the second, a B in the first column and an F in the second, etc. Using the semi-colons means it disregards the order.