Countif based on multiple criteria in multiple columns

[BungleNZ]

Hi

I'm struggling to get a countif formula working in a spreadsheet. With the example sheet I've posted, I'm trying for each "Bowler", I am trying to count, for example, how many times 0 runs are scored for the ball.

Any help anyone could provide would be much appreciated.

Thanks

Jamie

Excel Workbook
	G	H	I	J	K	L	M	N	O	P	Q
45	Bowler	Runs
46	Chris L	1	0	1				0	1
47	Chris L				-5	1	3
48	Trav		2			2
49	Trav	5		0	3		2
50	Miles			1			1
51	Miles	1	-5		0	-5		0
52	Dean	2				0	3
53	Dean		-5	0	-5
54	Pidge	2			1		2
55	Pidge		-5	2		2
56	Ryan	0	3			-5		1
57	Ryan			1	1		2
58	John	1				1
59	John		0	0	1		0
60	Dingo	2		0	1			3
61	Dingo		1			1	2		1
RD 1 - Game 1

Excel 2007

 

[energman58]

You could use a helper column say in column I with formulas like this:

In i46

=countif(J46:R46,"=0")

And a formula to count your totals like this:

= =SUMPRODUCT(--(H46:H61="Trav"),I46:I61)

 

[chicagodiceman]

what version of Excel are you using? If 2007 or greater, try COUNTIFS

 

[cgcamal]

Hi Jamie,

I'm not sure to understand, but try inserting in P46 the forrmula

=IF(G46=G47,COUNTIF(H46:O47,0),"")

and copy down.

Hope this helps.

Regards.