Excel Countifs/Sumproduct with mutil Or statement


Joined
Mar 2, 2017
Messages
2
Reaction score
0
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?
 
Ad

Advertisements

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,510
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!
 
Joined
Mar 2, 2017
Messages
2
Reaction score
0
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 ?
 
Ad

Advertisements

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,510
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top