Excell formula

[snowdrop1]

hello how can i write a formula to check and identify if say i have a list of 100 employees who have taken training courses..

say 20 of the 100 employees have taken the same course but on different dates...i need to identify the most current course?

what would my formula be?

 

[Richard Schollar]

We can't answer that without knowing how employees who have taken the course are identified. It sounds like a Countifwill work for you (or a COUNTIFS/SUMPRODUCT if you want to specify a date range). It would look something like this:

Excel Workbook
	A	B	C
1	Employee	Course Taken	Date
2	Richard	Baking	01-Jan
3	Bob	Archery	16-Jan
4	Sally	Archery	31-Jan
5	Sarah	Baking	15-Feb
6	Chris	Football	02-Mar
7	Mark	Baking	17-Mar
8	Tim	Rugby	01-Apr
9	Ted	Baking	16-Apr
10	Helen	Yoga	01-May
11	Ronda	Archery	16-May
12
13	Course	Number
14	Baking	4
15
16	to specify a date range:
17
18	Course	Month	Number
19	Archery	January	2
Sheet1

Excel 2002

Cell Formulas
Range	Formula
B14	=COUNTIF(B2:B11,A14)
C19	=SUMPRODUCT((B2:B11=A19)+0,(TEXT(C2:C11,"mmm")="Jan")+0)

 

[snowdrop1]

my list was extracted from access..
but on the list io have over 5000 entries of the same person taking the same course but on different dates.. i think i have worked it out..

ive filtered my report plus also used "MAX" ON the date ranges... it seems to have taken only the most current date,..!>

thanks