Multiple Criteria Count

[chullan88]

A	2
B	2
C	1
B	3
C	4

In the following table, I would like to count the no. of instances where the value in first column is A or B and value in 2nd column is 1 or 2.



So the count will be 2 in this case

[Sam Capricci]

=countifs(A:A,"A",B:B,2) Should work.

EDIT: too early reread, =countifs(A:A,"A",B:B,2)+countifs(A:A,"B",B:B,2) is one option (corrected first version with two conditions).

[chullan88]

Hi Sambo kid,

Your formula does not count for 1

[Phuocam]

Try:

=SUMPRODUCT(((A1:A10="A")+(A1:A10="B"))*((B1:B10=1)+(B1:B10=2)))

[chullan88]

Try:

=SUMPRODUCT(((A1:A10="A")+(A1:A10="B"))*((B1:B10=1)+(B1:B10=2)))

Works perfect!!!

[chullan88]

I was curious to know if any solution is posible giving the criteria within the curly brackets.( in case of large no. of criteria)

{1,2} {"A","B"}

[Phuocam]

I was curious to know if any solution
is posible giving the criteria within the curly brackets.
( in case of large no. of criteria)
{1,2} {"A","B"}

Here's ...

=SUM(COUNTIFS(A1:A10,{"A","B"},B1:B10,{1;2}))

[chullan88]

Thanks!!

I had tried the above formula once but couldn't get it right as I had used comma for sperating 1 and 2 within the curly brackets
But with the semicolon,its giving the correct result
Could you explain what is the difference?

[kev_]

or if you prefer a long-winded option

=COUNTIFS(A1:A5,"A",B1:B5,1)+COUNTIFS(A1:A5,"A",B1:B5,2)+COUNTIFS(A1:A5,"B",B1:B5,1)+COUNTIFS(A1:A5,"B",B1:B5,2)

[Sam Capricci]

Sorry, just got up and need caffeine, not only did I miss the B in the first formula but also the 1. Sumproduct is a better option.
looks like you got it solved though.