Help to find formula

nika

Board Regular
Joined
Sep 16, 2004
Messages
80
Hello all

I have a list of athlete data such as Name ,Club, Performance, Events, Match. In the match column is the number of the match they competed in such as Match 1, 2 etc. I need a formula to tell me how many matches an athlete has competed in.

Thanks in anticipation
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Ivan Howard

Active Member
Joined
Nov 10, 2004
Messages
332
Hi Nika,

Try using a countif statement, against the athlete's name. You could also build an "IF" statement into it to only look at past records/matches.

Let me know how you get on.

Ivan
 

nika

Board Regular
Joined
Sep 16, 2004
Messages
80
2004.2005 indoor data.xls
ABCDEFG
1AgeEventPosNamePerfClubMatch
2U13B6Lap1MichaelFeeney63.5A1
3U13B2Lap1JordanMartin19.4A1
4U13B2Lap2TomFenton23.1C1
5U13B2LapGMichaelFeeney19.4A1
6U13B2LapGSamGlenn21.1A1
7U13BVHJ1SamGlenn45A1
8U13BVHJGJordanMartin44A1
9U13BShot1JordanMartin6.19A1
10U13BShot2TomFenton5.51C1
11U13BSTJ1MichaelFeeney6.40A1
12U13BSTJ2TomFenton4.90C1
13U13BSTJGSamGlenn5.24A1
14U13BRelay1Chesterfield1.33.0RC2
15U13B2Lap1RobertScott19.5C2
16U13B2Lap2JordanMartin19.7A2
17U13B2Lap3GeorgeSlysz20.8K2
18U13B2LapGEdwardRichards19.8K2
19U13B2LapGTomFenton23.3C2
20U13B2LapGLukeBaranowski23.3C2
21U13B4Lap1MichaelFeeney42.0A2
22U13B4Lap2JamesBarker44.0K2
U13 & 15B&G
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Try:

=COUNT(1/FREQUENCY(IF(($D$2:$D$22=H1),$G$2:$G$22),$G$2:$G$22))

confirmed with Ctrl + shift + enter.

H1 holds the name to count.
 

nika

Board Regular
Joined
Sep 16, 2004
Messages
80
Fairwinds
Tried it and guess what? Perfection

Thankyou very much
 

Forum statistics

Threads
1,148,397
Messages
5,746,460
Members
424,021
Latest member
naimathulla

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
Top