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?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,717
Messages
5,660,474
Members
418,580
Latest member
IrishDave2137

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