Solved: Count if with multiple

[chungtinhlak]

Hello All, I normal use array count(if(.... do do a count if i only have 1 criteria but that's not the case anymore. I was hoping to get some help with the experts here.

for example
I have 3 columns (see below), i also have 3 columns on another sheet with 3 columns of what i want to column for, for example, i want a count of this
Column1 Column 2 Column 3
A 0 x
B 1 x
C 2 y

this would return 2, can i do this with just standard build in functions or i have to use vba?

thanks all for your help.

Column1 Column 2 Column 3
A 0 x
B 1 x
C 2 y
D 3 x
A 1 y
B 2 y
C 2 y
D 4 y

[Bob Phillips]

Can you explain how you get at those results?

[chungtinhlak]

i'm sorry, the result should be 4, the first 3 and the 2nd row from the bottom.

thanks

[Bob Phillips]

Try

=SUMPRODUCT(COUNTIFS(Sheet1!A:A,A1:A3,Sheet1!B:B,B1:B3,Sheet1!C:C,C1:C3))

[chungtinhlak]

I try it, but doesn't seem to work correctly. I have attached the file of better examples. Thanks for your help.

in M12:Q17

I want to count if A:A matched J2:J10 and B matched K2:K10 with the scores on each of the questions.

[Bob Phillips]

You've only got two conditions to match against whereas you originally said there were three, so you remove the last criteria range and criteria

=SUMPRODUCT(COUNTIFS($A:$A,J2:$J$10,$B:$B,$K$2:$K$10))

[chungtinhlak]

Edit:::

xld, I got it, my example wasn't good, that's why the result was incorrect, thanks a lot for your help.

The third criteria is the score, , on column L, in this case. I want a count of all the 0, 1, 2,3,4, and 5.

thank you

[chungtinhlak]

I thought it was solved but the countifs put the 2 3 criteria in an "and" statement i believe, is there a way to do an or.

For example

if i'm matching against A, B , C, D, and 1, 2, 4 and, xx, dd, yy

any of those combination, somehow the countifs returns much less record. Thanks for your help

[Bob Phillips]

On the basis of what I think you mean, this should do iy

=SUMPRODUCT(COUNTIFS($A$2:$A$40,J2:$J$10,$B$2:$B$40,$K$2:$K$10,C$2:C$40,(RO W($L$2:$L$10)^0)*$L12))

[chungtinhlak]

thanks, I think we're getting closer. Please see the attached file. It looks like everything gets counted correctly except for the stuff highlighted in red.

if you changed the value to anything other than 0-5, the total does not changed at all.

Column J and K contains all the unique value from A and B. L12 to L15 contains all the possible scores so technically, it should return a total of 39 record, but only return 28 and the 11 red record somehow didn't get counted.

thank you so much for your help

[Bob Phillips]

There is no combination of Bay 6 and Shelf 15 in K:L, so it won't be counted at all, and ditto for the others.

[chungtinhlak]

IC, so it won't do a combination with or. thanks xld

[Bob Phillips]

You mean any in K and/or any in L? Why not just test the value of the Question column?