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

Edit: I can easily get a formula to work by summing separate counts, but I'm trying to get one formula which will do the count based on the bowlers name in a summary sheet.

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

 

[shg]

Duplicated at http://www.mrexcel.com/forum/showthread.php?t=577989

 

[Trevor G]

Here is a snip of code that code help. BAsed on your range and names, you would need to adapt it, but in this case the answer would be 1 as Trav is the name in Cell A3

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> countcol()<br><SPAN style="color:#00007F">Dim</SPAN> strName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>strName = Range("A3")<br><SPAN style="color:#00007F">Dim</SPAN> countred <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("h45")<br><SPAN style="color:#00007F">If</SPAN> cell.Value = strName <SPAN style="color:#00007F">Or</SPAN> cell.Value = "0" <SPAN style="color:#00007F">Then</SPAN> countred = countred + 1<br><SPAN style="color:#00007F">Next</SPAN> cell<br>MsgBox countred<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Sample Table

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Bowler						Bowler	Runs
2	Chris L						Chris L	1	0	1				0	1
3	Trav						Chris L				-5	1	3
4	Miles						Trav		2			2
5	Dean						Trav	5		0	3		2
6	Pidge						Miles			1			1
7	Ryan						Miles	1	-5		0	-5		0
8	John						Dean	2				0	3
9	Dingo						Dean		-5	0	-5
10							Pidge	2			1		2
11							Pidge		-5	2		2
12							Ryan	0	3			-5		1
13							Ryan			1	1		2
14							John	1				1
15							John		0	0	1		0
16							Dingo	2		0	1			3
17							Dingo		1			1	2		1
Sheet1

 

[Trevor G]

Revised with only formula.

Excel Workbook
	A	B
1	Bowler	Count Zero
2	Chris L	2
3	Trav	1
4	Miles	2
5	Dean	2
6	Pidge	0
7	Ryan	1
8	John	3
9	Dingo	1
Sheet1