Excell formula

snowdrop1

banned
Joined
Jun 8, 2010
Messages
118
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?
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
ABC
1EmployeeCourse TakenDate
2RichardBaking01-Jan
3BobArchery16-Jan
4SallyArchery31-Jan
5SarahBaking15-Feb
6ChrisFootball02-Mar
7MarkBaking17-Mar
8TimRugby01-Apr
9TedBaking16-Apr
10HelenYoga01-May
11RondaArchery16-May
12
13CourseNumber
14Baking4
15
16to specify a date range:
17
18CourseMonthNumber
19ArcheryJanuary2
Sheet1
Excel 2002
Cell Formulas
RangeFormula
B14=COUNTIF(B2:B11,A14)
C19=SUMPRODUCT((B2:B11=A19)+0,(TEXT(C2:C11,"mmm")="Jan")+0)
 

snowdrop1

banned
Joined
Jun 8, 2010
Messages
118
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,198
Messages
5,509,770
Members
408,752
Latest member
KrisF

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top