Counting the Number of people on a course

Hi

I hope someone can help with this one:

I have a list of people who attended different training course:

EG

Name Surname Course title start date
Dan Glencross Excel Level 1 26-Jun
Joanna Reddy Excel Level 1 26-Jun
Jon Shewen Excel Level 1 26-Jun
Mark Musto Excel Level 1 07-Feb
Mark Treadwell Excel Level 1 07-Feb
Peter Edgar Excel Level 2 07-Feb
Richard Davey Excel Level 2 07-Feb
Stephen Baker Excel Level 2 07-Feb


I have worked out how to diffrentate where which people of are on each course using =IF(AND(C2=C3,D2=D3),0,1)

Name Surname Course title start date IF, AND Formula
Dan Glencross Excel Level 1 26-Jun 0
Joanna Reddy Excel Level 1 26-Jun 0
Jon Shewen Excel Level 1 26-Jun 1
Mark Musto Excel Level 1 07-Feb 0
Mark Treadwell Excel Level 1 07-Feb 1
Peter Edgar Excel Level 2 07-Feb 0
Richard Davey Excel Level 2 07-Feb 0
Stephen Baker Excel Level 2 07-Feb 1

I can see manually that 3 people under took the Excel Level 1 on 26-Jun by counting the number of 0 until I hit a 1 and then adding the number of rows (eg. 3) but I want a formula to do it for me. Can anyone help? I have attacehd my working spreadsheet Problem.xlsx

Sorry MOVED to Excel General

http://www.excelforum.com/excel-general/846113-counting-the-number-of-people-on-a-course.html?p=2862428

abousetta,

Thanks

Put this formula in F2:

=COUNTIFS(C$2:C$9,C2,D$2:D$9,D2)

and copy down.

Hope this helps.

Pete