Countif with matrix

[Rubsie]

Hi,

I have the following table and I would like to count the amount of rows in which either column x, y OR z has a value greater than 0. I'm assuming this has to be done with an array formule as I do not want to create a helper column.
In the example below, the result of this calculation is 5 (rows A, C, D, F and G have a value in one of the 3 columns greater than 0).

	x	y	z
A	0	1	0
B	0	0	0
C	1	1	1
D	0	0	1
E	0	0	0
F	1	1	0
G	0	1	1

<tbody>
</tbody>

Does anyone know how to do this?

 

[XOR LX]

So you do need either mine or Markmzz's formula!

 

[Aladin Akyurek]

That would give 4 as a result.

Right... As already posted, the frequency formula becomes:

=SUM(IF(FREQUENCY(IF((B2:B8>0)+(C2:C8>1)+(D2:D8>2),ROW(A2:A8)),ROW(A2:A8)),1))

which you need to confirm with control+shift+enter.

 

[XOR LX]

Yes, that as well, of course.

 

[Marcelo Branco]

That would give 4 as a result.

So, adjusting the ranges, try my formula in #17

=SUMPRODUCT(--(((A2:A8>0)+(B2:B8>1)+(C2:C8>2))>0))

M.

 

[markmzz]

And Markmzz - appears I may owe you an apology!

Regards

Hi XOR LX,

No problem and lets wait for the user.

Markmzz