Countif with multiple criteria

HI, I have a problem.

I would like to count number of time X gets more than 0 for Test 1 or Test 2.
Therefore, as long as he gets more than 0 for 1 of the test (T1 or T2), i want to count. Results should give me 3 in this case.


A | T1 |T2
X | 21 | 00
X | 00 | 23
X | 00 | 00
Y | 00 | 45
Y | 34 | 00
X | 21 |55

Thanks!

What is passing? What is C? Is that the column?

Assuming A2:A7 is X or Y
B2:B7, C2:C7 is value
I wonder if B2:C7 was value or text-stored-as number or not, but try:

=COUNTIFS(A2:A7,"X",--B2:B7,">0")

Originally Posted by heartfeltyeo

Hello, the formulae gave me error.

Yes B2: C7 Is number.

What kind of error did you get?
Or try to remove"--"?

Please Login or Register  to view this content.

Originally Posted by bebo021999

=COUNTIFS(A2:A7,"X",--B2:B7,">0")

COUNTIFS won't accept that syntax --B2:B7.

When your example is converted to excel values on a worksheet, spaces are introduced if not careful so that may account for errors. Here are two methods of counting for you. I have entered X and Y in F2 and F3 and used those cells as references in the formulae.
Enter in G2 and fill down

Formula:

Please Login or Register  to view this content.

OR

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E	F	G
1	A	T1	T2
2	X	21	0			X	4
3	X	0	23			y	2
4	X	0	0
5	Y	0	45
6	Y	34	0
7	X	21	55

Here's yet another one...

=SUMPRODUCT(--(A2:A7="X"),--(MMULT(--(B2:C7>0),{1;1})>0))

One more for good measure...

=SUMPRODUCT(--(A2:A7="X"),SIGN((B2:B7>0)+(C2:C7>0)))