looking for a formula

[jimboayr]

Have had a lot of help in the past hope someone can help me again.
What I have is a table with various headings the ones that I'm concerned about are the assists.
I need to find out how many assits player x has - at the moment i'm using an auto filter to filter out player x in the first column (H) and counting them manually and then doing an auto filter for player x in column (I) counting them and then adding the two together.
Not the ideal situation - could do with a formula that can tell me that in the two columns player x appears x amount of times.

Goals.xls
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Comp	Home			Away	Goal	Assist	Assist	Time	Type
2	06/09/08	CC	Basingstoke	1	0	Coventry	BradCruikshank	EricBraff	GregChambers	2.39	EH
3	06/09/08	CC	Basingstoke	1	1	Coventry	ScottKelman	AdamCalder	JonathanWeaver	20.28	PP
4	06/09/08	CC	Basingstoke	1	2	Coventry	SylvainDeschatelets	DanCarlson	AdamCalder	21.17	EH
5	06/09/08	CC	Basingstoke	1	3	Coventry	JonathanWeaver	DanCarlson	ScottKelman	27.58	PP
6	06/09/08	CC	Basingstoke	1	4	Coventry	BarrieMoore	JonathanWeaver	CarlyleLewis	29.21	PP
7	06/09/08	CC	Basingstoke	2	4	Coventry	BradCruikshank	BrentHughes	GregChambers	32.52	EH
8	06/09/08	LEA	Hull	0	1	Manchester	DavidBeauregard	NathanWard	TonyHand	0.24	EH
9	06/09/08	LEA	Hull	0	2	Manchester	AlexDunn	KyleBruce	JoshGarbutt	12.18	SH
10	06/09/08	LEA	Hull	0	3	Manchester	AdamWalker	LukeBoothroyd		12.44	SH
11	06/09/08	LEA	Hull	1	3	Manchester	JeffGlowa	PavelGomenyuk		14.02	EH
12	06/09/08	LEA	Hull	2	3	Manchester	RichardKozak			29.07	PP
13	06/09/08	LEA	Hull	3	3	Manchester	SlavaKoulikov	MatthewReynolds	JasonKostadine	32.47	EH
14	06/09/08	LEA	Hull	3	4	Manchester	DavidBeauregard	JoshGarbutt		46.18	PP
15	06/09/08	LEA	Belfast	1	0	Cardiff	AndrewMartin	MichaelBurgoyne	PaulDeniset	11.42	SH
16	06/09/08	LEA	Belfast	1	1	Cardiff	MikePrpich	JasonSilverthorn	WesJarvis	19.12	EH
17	06/09/08	LEA	Belfast	1	2	Cardiff	JasonSilverthorn	BradVoth	MichaelHartwick	19.43	EH
18	06/09/08	LEA	Belfast	2	2	Cardiff	AndrewMartin	BobbyRobins	PaulDeniset	36.39	EH
19	06/09/08	LEA	Belfast	3	2	Cardiff	AndrewMartin	BobbyRobins	MichaelBurgoyne	41.34	PP
20	06/09/08	LEA	Belfast	3	3	Cardiff	BradVoth	WesJarvis	PhilHill	43.16	EH
21	06/09/08	LEA	Belfast	4	3	Cardiff	BobbyRobins	PaulDeniset	ShaneJohnson	52.00	EH
22	06/09/08	LEA	Belfast	5	3	Cardiff	BobbyRobins	AndrewMartin	GraemeWalton	52.46	EH
23	06/09/08	LEA	Newcastle	0	1	Nottingham	KevinBergin	JohanMolin	CoreyNeilson	14.52	PP
24	06/09/08	LEA	Newcastle	1	1	Nottingham	WarrenTait	BenCampbell		30.26	EH
25	06/09/08	LEA	Newcastle	1	2	Nottingham	MattMyers	RobertLachowicz	BrendanCook	44.24	EH
26	06/09/08	LEA	Newcastle	2	2	Nottingham	DerekCampbell	RobWilson	MarkGouett	51.40	PP
27	06/09/08	LEA	Newcastle	2	3	Nottingham	DavidClarke	JohanMolin	BruceRichardson	55.14	PP
28	06/09/08	LEA	Sheffield	0	1	Edinburgh	TaylorChristie	ShawnGermain		5.35	EH
29	06/09/08	LEA	Sheffield	0	2	Edinburgh	DougChristiansen	AdamStefishen	TaylorChristie	8.44	PP
30	06/09/08	LEA	Sheffield	0	3	Edinburgh	DougChristiansen	AndrejRajcak	TaylorChristie	29.19	PP
31	06/09/08	LEA	Sheffield	1	3	Edinburgh	JeffLegue	RodSarich	RobertDowd	52.26	EH
Scoring (3)

 

[Colin Legg]

Hi,

So if you wanted to know how many goal assists Dan Carlson has, you could use COUNTIF() like this:

=COUNTIF(H2:I31,"Dan Carlson")

If you put the text "Dan Carlson" in another cell, say cell A100, then you could also use:

=COUNTIF(H2:I31,A100)

A non-formula approach which might also be good here would be to use a pivot table.


Does that help?

 

[stanleydgromjr]

jimboayr,

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Comp	Home			Away	Goal	Assist	Assist	Time	Type
2	6/9/2008	CC	Basingstoke	1	0	Coventry	*Brad*Cruikshank	*Eric*Braff	*Greg*Chambers	2.39	EH
3	6/9/2008	CC	Basingstoke	1	1	Coventry	*Scott*Kelman	*Adam*Calder	*Jonathan*Weaver	20.28	PP
4	6/9/2008	CC	Basingstoke	1	2	Coventry	*Sylvain*Deschatelets	*Dan*Carlson	*Adam*Calder	21.17	EH
5	6/9/2008	CC	Basingstoke	1	3	Coventry	*Jonathan*Weaver	*Dan*Carlson	*Scott*Kelman	27.58	PP
6	6/9/2008	CC	Basingstoke	1	4	Coventry	*Barrie*Moore	*Jonathan*Weaver	*Carlyle*Lewis	29.21	PP
7	6/9/2008	CC	Basingstoke	2	4	Coventry	*Brad*Cruikshank	*Brent*Hughes	*Greg*Chambers	32.52	EH
8	6/9/2008	LEA	Hull	0	1	Manchester	*David*Beauregard	*Nathan*Ward	*Tony*Hand	0.24	EH
9	6/9/2008	LEA	Hull	0	2	Manchester	*Alex*Dunn	*Kyle*Bruce	*Josh*Garbutt	12.18	SH
10	6/9/2008	LEA	Hull	0	3	Manchester	*Adam*Walker	*Luke*Boothroyd		12.44	SH
11	6/9/2008	LEA	Hull	1	3	Manchester	*Jeff*Glowa	*Pavel*Gomenyuk		14.02	EH
12	6/9/2008	LEA	Hull	2	3	Manchester	*Richard*Kozak			29.07	PP
13	6/9/2008	LEA	Hull	3	3	Manchester	*Slava*Koulikov	*Matthew*Reynolds	*Jason*Kostadine	32.47	EH
14	6/9/2008	LEA	Hull	3	4	Manchester	*David*Beauregard	*Josh*Garbutt		46.18	PP
15	6/9/2008	LEA	Belfast	1	0	Cardiff	*Andrew*Martin	*Michael*Burgoyne	*Paul*Deniset	11.42	SH
16	6/9/2008	LEA	Belfast	1	1	Cardiff	*Mike*Prpich	*Jason*Silverthorn	*Wes*Jarvis	19.12	EH
17	6/9/2008	LEA	Belfast	1	2	Cardiff	*Jason*Silverthorn	*Brad*Voth	*Michael*Hartwick	19.43	EH
18	6/9/2008	LEA	Belfast	2	2	Cardiff	*Andrew*Martin	*Bobby*Robins	*Paul*Deniset	36.39	EH
19	6/9/2008	LEA	Belfast	3	2	Cardiff	*Andrew*Martin	*Bobby*Robins	*Michael*Burgoyne	41.34	PP
20	6/9/2008	LEA	Belfast	3	3	Cardiff	*Brad*Voth	*Wes*Jarvis	*Phil*Hill	43.16	EH
21	6/9/2008	LEA	Belfast	4	3	Cardiff	*Bobby*Robins	*Paul*Deniset	*Shane*Johnson	52	EH
22	6/9/2008	LEA	Belfast	5	3	Cardiff	*Bobby*Robins	*Andrew*Martin	*Graeme*Walton	52.46	EH
23	6/9/2008	LEA	Newcastle	0	1	Nottingham	*Kevin*Bergin	*Johan*Molin	*Corey*Neilson	14.52	PP
24	6/9/2008	LEA	Newcastle	1	1	Nottingham	*Warren*Tait	*Ben*Campbell		30.26	EH
25	6/9/2008	LEA	Newcastle	1	2	Nottingham	*Matt*Myers	*Robert*Lachowicz	*Brendan*Cook	44.24	EH
26	6/9/2008	LEA	Newcastle	2	2	Nottingham	*Derek*Campbell	*Rob*Wilson	*Mark*Gouett	51.4	PP
27	6/9/2008	LEA	Newcastle	2	3	Nottingham	*David*Clarke	*Johan*Molin	*Bruce*Richardson	55.14	PP
28	6/9/2008	LEA	Sheffield	0	1	Edinburgh	*Taylor*Christie	*Shawn*Germain		5.35	EH
29	6/9/2008	LEA	Sheffield	0	2	Edinburgh	*Doug*Christiansen	*Adam*Stefishen	*Taylor*Christie	8.44	PP
30	6/9/2008	LEA	Sheffield	0	3	Edinburgh	*Doug*Christiansen	*Andrej*Rajcak	*Taylor*Christie	29.19	PP
31	6/9/2008	LEA	Sheffield	1	3	Edinburgh	*Jeff*Legue	*Rod*Sarich	*Robert*Dowd	52.26	EH
Sheet1

Column M contains a list of unique names.

Excel Workbook
	M	N
1	Assist
2	*Adam*Calder	2
3	*Adam*Stefishen	1
4	*Andrej*Rajcak	1
5	*Andrew*Martin	1
6	*Ben*Campbell	1
7	*Bobby*Robins	2
8	*Brad*Voth	1
9	*Brendan*Cook	1
10	*Brent*Hughes	1
11	*Bruce*Richardson	1
12	*Carlyle*Lewis	1
13	*Corey*Neilson	1
14	*Dan*Carlson	2
15	*Eric*Braff	1
16	*Graeme*Walton	1
17	*Greg*Chambers	2
18	*Jason*Kostadine	1
19	*Jason*Silverthorn	1
20	*Johan*Molin	2
21	*Jonathan*Weaver	2
22	*Josh*Garbutt	2
23	*Kyle*Bruce	1
24	*Luke*Boothroyd	1
25	*Mark*Gouett	1
26	*Matthew*Reynolds	1
27	*Michael*Burgoyne	2
28	*Michael*Hartwick	1
29	*Nathan*Ward	1
30	*Paul*Deniset	3
31	*Pavel*Gomenyuk	1
32	*Phil*Hill	1
33	*Rob*Wilson	1
34	*Robert*Dowd	1
35	*Robert*Lachowicz	1
36	*Rod*Sarich	1
37	*Scott*Kelman	1
38	*Shane*Johnson	1
39	*Shawn*Germain	1
40	*Taylor*Christie	2
41	*Tony*Hand	1
42	*Wes*Jarvis	2
Sheet1

The formula in cell N2 (copied down):
=COUNTIF($H$2:$I$100,$M2)


Have a great day,
Stan

 

[jimboayr]

Hi Stan,

Sorry for the delay in getting back to you regards your reply.
What is the reason behind the asterix's and how do you get them to appear without having to put them in one by one.