looking for a formula

jimboayr

Board Regular
Joined
Oct 9, 2005
Messages
120
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
ABCDEFGHIJK
1DateCompHomeAwayGoalAssistAssistTimeType
206/09/08CCBasingstoke10CoventryBradCruikshankEricBraffGregChambers2.39EH
306/09/08CCBasingstoke11CoventryScottKelmanAdamCalderJonathanWeaver20.28PP
406/09/08CCBasingstoke12CoventrySylvainDeschateletsDanCarlsonAdamCalder21.17EH
506/09/08CCBasingstoke13CoventryJonathanWeaverDanCarlsonScottKelman27.58PP
606/09/08CCBasingstoke14CoventryBarrieMooreJonathanWeaverCarlyleLewis29.21PP
706/09/08CCBasingstoke24CoventryBradCruikshankBrentHughesGregChambers32.52EH
806/09/08LEAHull01ManchesterDavidBeauregardNathanWardTonyHand0.24EH
906/09/08LEAHull02ManchesterAlexDunnKyleBruceJoshGarbutt12.18SH
1006/09/08LEAHull03ManchesterAdamWalkerLukeBoothroyd12.44SH
1106/09/08LEAHull13ManchesterJeffGlowaPavelGomenyuk14.02EH
1206/09/08LEAHull23ManchesterRichardKozak29.07PP
1306/09/08LEAHull33ManchesterSlavaKoulikovMatthewReynoldsJasonKostadine32.47EH
1406/09/08LEAHull34ManchesterDavidBeauregardJoshGarbutt46.18PP
1506/09/08LEABelfast10CardiffAndrewMartinMichaelBurgoynePaulDeniset11.42SH
1606/09/08LEABelfast11CardiffMikePrpichJasonSilverthornWesJarvis19.12EH
1706/09/08LEABelfast12CardiffJasonSilverthornBradVothMichaelHartwick19.43EH
1806/09/08LEABelfast22CardiffAndrewMartinBobbyRobinsPaulDeniset36.39EH
1906/09/08LEABelfast32CardiffAndrewMartinBobbyRobinsMichaelBurgoyne41.34PP
2006/09/08LEABelfast33CardiffBradVothWesJarvisPhilHill43.16EH
2106/09/08LEABelfast43CardiffBobbyRobinsPaulDenisetShaneJohnson52.00EH
2206/09/08LEABelfast53CardiffBobbyRobinsAndrewMartinGraemeWalton52.46EH
2306/09/08LEANewcastle01NottinghamKevinBerginJohanMolinCoreyNeilson14.52PP
2406/09/08LEANewcastle11NottinghamWarrenTaitBenCampbell30.26EH
2506/09/08LEANewcastle12NottinghamMattMyersRobertLachowiczBrendanCook44.24EH
2606/09/08LEANewcastle22NottinghamDerekCampbellRobWilsonMarkGouett51.40PP
2706/09/08LEANewcastle23NottinghamDavidClarkeJohanMolinBruceRichardson55.14PP
2806/09/08LEASheffield01EdinburghTaylorChristieShawnGermain5.35EH
2906/09/08LEASheffield02EdinburghDougChristiansenAdamStefishenTaylorChristie8.44PP
3006/09/08LEASheffield03EdinburghDougChristiansenAndrejRajcakTaylorChristie29.19PP
3106/09/08LEASheffield13EdinburghJeffLegueRodSarichRobertDowd52.26EH
Scoring (3)
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi,

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

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

If you put the text "Dan Carlson" in another cell, say cell A100, then you could also use:
Code:
=COUNTIF(H2:I31,A100)

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


Does that help?
 
Upvote 0
jimboayr,

Excel Workbook
ABCDEFGHIJK
1DateCompHomeAwayGoalAssistAssistTimeType
26/9/2008CCBasingstoke10Coventry*Brad*Cruikshank*Eric*Braff*Greg*Chambers2.39EH
36/9/2008CCBasingstoke11Coventry*Scott*Kelman*Adam*Calder*Jonathan*Weaver20.28PP
46/9/2008CCBasingstoke12Coventry*Sylvain*Deschatelets*Dan*Carlson*Adam*Calder21.17EH
56/9/2008CCBasingstoke13Coventry*Jonathan*Weaver*Dan*Carlson*Scott*Kelman27.58PP
66/9/2008CCBasingstoke14Coventry*Barrie*Moore*Jonathan*Weaver*Carlyle*Lewis29.21PP
76/9/2008CCBasingstoke24Coventry*Brad*Cruikshank*Brent*Hughes*Greg*Chambers32.52EH
86/9/2008LEAHull01Manchester*David*Beauregard*Nathan*Ward*Tony*Hand0.24EH
96/9/2008LEAHull02Manchester*Alex*Dunn*Kyle*Bruce*Josh*Garbutt12.18SH
106/9/2008LEAHull03Manchester*Adam*Walker*Luke*Boothroyd12.44SH
116/9/2008LEAHull13Manchester*Jeff*Glowa*Pavel*Gomenyuk14.02EH
126/9/2008LEAHull23Manchester*Richard*Kozak29.07PP
136/9/2008LEAHull33Manchester*Slava*Koulikov*Matthew*Reynolds*Jason*Kostadine32.47EH
146/9/2008LEAHull34Manchester*David*Beauregard*Josh*Garbutt46.18PP
156/9/2008LEABelfast10Cardiff*Andrew*Martin*Michael*Burgoyne*Paul*Deniset11.42SH
166/9/2008LEABelfast11Cardiff*Mike*Prpich*Jason*Silverthorn*Wes*Jarvis19.12EH
176/9/2008LEABelfast12Cardiff*Jason*Silverthorn*Brad*Voth*Michael*Hartwick19.43EH
186/9/2008LEABelfast22Cardiff*Andrew*Martin*Bobby*Robins*Paul*Deniset36.39EH
196/9/2008LEABelfast32Cardiff*Andrew*Martin*Bobby*Robins*Michael*Burgoyne41.34PP
206/9/2008LEABelfast33Cardiff*Brad*Voth*Wes*Jarvis*Phil*Hill43.16EH
216/9/2008LEABelfast43Cardiff*Bobby*Robins*Paul*Deniset*Shane*Johnson52EH
226/9/2008LEABelfast53Cardiff*Bobby*Robins*Andrew*Martin*Graeme*Walton52.46EH
236/9/2008LEANewcastle01Nottingham*Kevin*Bergin*Johan*Molin*Corey*Neilson14.52PP
246/9/2008LEANewcastle11Nottingham*Warren*Tait*Ben*Campbell30.26EH
256/9/2008LEANewcastle12Nottingham*Matt*Myers*Robert*Lachowicz*Brendan*Cook44.24EH
266/9/2008LEANewcastle22Nottingham*Derek*Campbell*Rob*Wilson*Mark*Gouett51.4PP
276/9/2008LEANewcastle23Nottingham*David*Clarke*Johan*Molin*Bruce*Richardson55.14PP
286/9/2008LEASheffield01Edinburgh*Taylor*Christie*Shawn*Germain5.35EH
296/9/2008LEASheffield02Edinburgh*Doug*Christiansen*Adam*Stefishen*Taylor*Christie8.44PP
306/9/2008LEASheffield03Edinburgh*Doug*Christiansen*Andrej*Rajcak*Taylor*Christie29.19PP
316/9/2008LEASheffield13Edinburgh*Jeff*Legue*Rod*Sarich*Robert*Dowd52.26EH
Sheet1



Column M contains a list of unique names.


Excel Workbook
MN
1Assist
2*Adam*Calder2
3*Adam*Stefishen1
4*Andrej*Rajcak1
5*Andrew*Martin1
6*Ben*Campbell1
7*Bobby*Robins2
8*Brad*Voth1
9*Brendan*Cook1
10*Brent*Hughes1
11*Bruce*Richardson1
12*Carlyle*Lewis1
13*Corey*Neilson1
14*Dan*Carlson2
15*Eric*Braff1
16*Graeme*Walton1
17*Greg*Chambers2
18*Jason*Kostadine1
19*Jason*Silverthorn1
20*Johan*Molin2
21*Jonathan*Weaver2
22*Josh*Garbutt2
23*Kyle*Bruce1
24*Luke*Boothroyd1
25*Mark*Gouett1
26*Matthew*Reynolds1
27*Michael*Burgoyne2
28*Michael*Hartwick1
29*Nathan*Ward1
30*Paul*Deniset3
31*Pavel*Gomenyuk1
32*Phil*Hill1
33*Rob*Wilson1
34*Robert*Dowd1
35*Robert*Lachowicz1
36*Rod*Sarich1
37*Scott*Kelman1
38*Shane*Johnson1
39*Shawn*Germain1
40*Taylor*Christie2
41*Tony*Hand1
42*Wes*Jarvis2
Sheet1



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


Have a great day,
Stan
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,218,946
Messages
6,145,383
Members
450,613
Latest member
Chalky1982

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top