I want to count 0 in an array with 5Rows and 5Columns Rowwise

[CA_Punit]

I have an array which gives the following result

Book1
	AB	AC	AD	AE	AF	AG	AH
4
5		0	1	1	1	1
6		0	0	1	1	1
7		0	0	0	1	1
8		0	0	0	0	0
9		0	0	0	0	0
10		0	1	1	1	1
11
12
13
14
Sheet2

Cell Formulas
Range	Formula
AC5:AG10	AC5	=--(Z1:Z6>AB1:AF1)
Dynamic array formulas.

I want to return count of Zero row Wise Like this in an arrray

Book1
	AJ
5	1
6	2
7	3
8	5
9	5
10	1
Sheet2

How to do it ( The range specified above is using Dynamic array formula ) and I want the result to display count in only one range as stated in 2nd sheet

 

[CA_Punit]

Got the Answer Using Mmult function it can be done.

Is there any other option using combination of rows and column function.

 

[Rick Rothstein]

What about this...

=COUNTIF(AC5:AG5,0)

And, if you do not copy this down where the range is blank, this...

=5-SUM(AC5:AG5)

 

[mikerickson]

What result do you want from that data.
I suspect that the MMULT result might not be as robust as you are hoping.
Is your actual data as structured as the example (only 0 and 1 all the 0 on the left)

 

[CA_Punit]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	I Want to Sort the Data in Ascending Order
2
3	Z
4	A
5	B
6	Q
7	W
8	A
9
10	So I Did this
11
12	0	1	1	1	1	1
13	0	0	0	0	0	0
14	0	1	0	0	0	1
15	0	1	1	0	0	1
16	0	1	1	1	0	1
17	0	0	0	0	0	0
18
19	So I Needed to count the Numbers of Zero Row-Wise. So Used MMULT Function
20
21	1
22	6
23	4
24	3
25	2
26	6
27
28	But I am not getting the Data Sorted in Ascending Order. I Don’t want to use Dynamic Array Formula like Sorted.
29
30	A
31	A
32	Q
33	B
34	A
35	Z
36
37
38
39
Sheet4

Cell Formulas
Range	Formula
A12:F17	A12	=--(A3:A8>TRANSPOSE(A3:A8))
A21:A26	A21	=6-MMULT(A12#,(ROW(A12:A17)-ROW(A12)+1)^0)
A30:A35	A30	=INDEX(A3:A8,LARGE(A21#,(ROW(A12:A17)-ROW(A12)+1)))
Dynamic array formulas.

 

[mikerickson]

You can get the same result by putting =COUNTIF(A12:F12,0) in A21 and drag it down.

But your overall goal of sorting with a formula..

put this formula in A30
=INDEX(A:A, MOD(SMALL(COUNTIF($A$3:$A$8,"<"&$A$3:$A$8)+ROW($A$3:$A$8)/100,ROW(A1)), 1)*100,1)
enter it with Ctrl-Shift-Enter

and then drag it downwards.
If you want it sorted descenting change the < to >

 

[CA_Punit]

"+ROW($A$3:$A$8)/100"

Great Trick sir, Made my day.
Will definetly apply in future.