Conditional COUNTIF() on a whole row

[stonypaul]

Greetings, I have a worksheet with 737 columns; A1:ABI64 - below is a small representation of it. In cells B11:B18 I want to add a formula to reference the person in column A and count the 0's for that person, in the range A1:ABI64. What is the best way of doing this please? I have tried arrayformula() sumproduct() and a combination of sum() and lookup() but to no avail. Thank you.

	A	B	C	D	E	F	G
1	Brad	1	0	0	2	1	1
2	Brandon	5	4	6	6	5	7
3	Brian	0	6	7	1	7	8
4	Chris	9	7	5	10	8	6
5	Clement	0	0	7	1	1	8
6	Dale	1	0	9	2	1	10
7	Dan	8	2	9	9	3	10
8	Dario	0	3	0	1	4	1
9
10
11	Brad
12	Brandon
13	Brian
14	Chris
15	Clement
16	Dale
17	Dan
18	Dario

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

However, see if this is what you want.

20 12 17.xlsm
	A	B	C	D	E	F	G
1
2	Brad	1	0	0	2	1	1
3	Brandon	5	4	6	6	5	7
4	Brian	0	6	7	1	7	8
5	Chris	9	7	5	10	8	6
6	Clement	0	0	7	1	1	8
7	Dale	1	0	9	2	1	10
8	Dan	8	2	9	9	3	10
9	Dario	0	3	0	1	4	1
10
11
12	Brad	2
13	Brandon	0
14	Brian	1
15	Chris	0
16	Clement	2
17	Dale	1
18	Dan	0
19	Dario	2
Count Zero

Cell Formulas
Range	Formula
B12:B19	B12	=COUNTIF(INDEX(B$2:G$9,MATCH(A12,A$2:A$9,0),0),0)

 

[stonypaul]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

However, see if this is what you want.

Thank you, that works like a charm

 

[Peter_SSs]

You're welcome. Glad it worked for you.

What about the Account details update?