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?

 

[Rubsie]

An exhibit for clarification along with the desired result would be helpful. Guessing from he short specs:

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

which must be confirmed with control+shift+enter.

Thank you Aladin, this formula works great.
XOR LX, I can't seem to get yours working. I'd like to add the workbook with those two formula's but I do not have permission do so..

 

[barry houdini]

if you want to count a row when all of the conditions are satisfied try COUNTIFS like this

=COUNTIFS(A1:A7,">0",B1:B7,">1",C1:C7,">2")

 

[Aladin Akyurek]

Thank you Aladin, this formula works great.
...

You are welcome. I've used FREQUENCY in continuation of the first question, although, as noted, CountIfs would also suffice. No idea what would be a better performer.

 

[Rubsie]

if you want to count a row when all of the conditions are satisfied try COUNTIFS like this

=COUNTIFS(A1:A7,">0",B1:B7,">1",C1:C7,">2")

Not all criteria have to be met, if only one (or two or all three) of them is met, it should count the row. That's why countifs doesn't work for me.

 

[Aladin Akyurek]

Not all criteria have to be met, if only one (or two or all three) of them is met, it should count the row. That's why countifs doesn't work for me.

Bit then, what is the count regarding the exhibit that follows?

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

<tbody>
</tbody>

 

[markmzz]

Not all criteria have to be met, if only one (or two or all three) of them is met, it should count the row. That's why countifs doesn't work for me.

Hi Rubsie,

Did you try my formula of the post #9?

If yes, what you got?

Here is the result with that formula:

	x	y	z		Result
A	0	1	3		4
B	0	0	0
C	1	2	3
D	0	0	1
E	0	0	0
F	1	4	5
G	1	1	1
****	****	****	****	**	*******

<tbody>
</tbody>

Markmzz

 

[Marcelo Branco]

Assuming data in A1:C7, maybe...

=SUMPRODUCT(--(((A1:A7>0)+(B1:B7>1)+(C1:C7>2))>0))

M.

 

[XOR LX]

I'm confused now.

If I now understand you correctly, my formula needs a slight amendment to:

=SUM(0+(MMULT(0+(A1:C7>{0,1,2}),TRANSPOSE(COLUMN(A1:C7))^0)>0))

And Markmzz - appears I may owe you an apology!

Regards

 

[Aladin Akyurek]

Bit then, what is the count regarding the exhibit that follows?

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

<tbody>
</tbody>

Assuming data in A1:C7, maybe...

=SUMPRODUCT(--(((A1:A7>0)+(B1:B7>1)+(C1:C7>2))>0))

M.

If that is the interpretation (what is desired), just for the record:

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

 

[Rubsie]

Bit then, what is the count regarding the exhibit that follows?

	x (>0)	y (>1)	z (>2)
A	0	1	0
B	1	0	0
C	1	2	3
D	0	0	1
E	1	2	0
F	1	2	5
G	0	1	1

<tbody>
</tbody>

That would give 4 as a result.