Calculating First Time Pass Rate

[macattackpro]

Greetings!

I was unable to find anything similar to my issue and been banging my head on this all day.

I have a table with multiple columns of training information. I need to figure a first time pass rate for each course by each individual. I do have a unique PSID for each employee, a unique code for each course, and a unique confirmation number for each time they enroll in the course. Passing grade is 80%. I have the below formula that works up until calculating whether the row being evaluated has the lowest confirmation number for that employee and that course. Ideas?

{=IF($K2="Completed",COUNTIFS($D$2:$D$50000,$D2,$J$2:$J$50000,$J2,$L2,">=80"),"")}

[TABLE="class: grid, width: 2132, align: left"]
<tbody>[TR]
[TD]Learner Status
[/TD]
[TD]Department ID
[/TD]
[TD]Department
[/TD]
[TD]PSID
[/TD]
[TD]Hire Date
[/TD]
[TD]Rehire Date
[/TD]
[TD]First Name
[/TD]
[TD]Last Name
[/TD]
[TD]Course
[/TD]
[TD]Course Code
[/TD]
[TD]Status
[/TD]
[TD]Score
[/TD]
[TD]Completion Date
[/TD]
[TD]Confirmation
[/TD]
[TD]FTPR
[/TD]
[/TR]
[TR]
[TD]Active
[/TD]
[TD]8041
[/TD]
[TD]Acme
[/TD]
[TD]1234
[/TD]
[TD]2/4/2002
[/TD]
[TD]11/20/2017
[/TD]
[TD]Robert
[/TD]
[TD]Smith
[/TD]
[TD]Information Resource Use Agreement
[/TD]
[TD]2015_IRUA
[/TD]
[TD]Completed
[/TD]
[TD]100
[/TD]
[TD]9/13/2017
[/TD]
[TD]2604534
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Active
[/TD]
[TD]8041
[/TD]
[TD]Acme
[/TD]
[TD]1234
[/TD]
[TD]2/4/2002
[/TD]
[TD]11/20/2017
[/TD]
[TD]Robert
[/TD]
[TD]Smith
[/TD]
[TD]Information Resource Use Agreement
[/TD]
[TD]2017_IRUA
[/TD]
[TD]Completed
[/TD]
[TD]100
[/TD]
[TD]10/11/2017
[/TD]
[TD]2646747
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Active
[/TD]
[TD]8041
[/TD]
[TD]Acme
[/TD]
[TD]1234
[/TD]
[TD]2/4/2002
[/TD]
[TD]11/20/2017
[/TD]
[TD]Robert
[/TD]
[TD]Smith
[/TD]
[TD]Information Resource Use Agreement
[/TD]
[TD]2017_IRUA
[/TD]
[TD]In-Progress
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2757344
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Active
[/TD]
[TD]8041
[/TD]
[TD]Acme
[/TD]
[TD]1234
[/TD]
[TD]2/4/2002
[/TD]
[TD]11/20/2017
[/TD]
[TD]Robert
[/TD]
[TD]Smith
[/TD]
[TD]OIG Ethics Training
[/TD]
[TD]ET2015
[/TD]
[TD]Completed
[/TD]
[TD]100
[/TD]
[TD]9/13/2017
[/TD]
[TD]2604912
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Active
[/TD]
[TD]8041
[/TD]
[TD]Acme
[/TD]
[TD]1234
[/TD]
[TD]2/4/2002
[/TD]
[TD]11/20/2017
[/TD]
[TD]Robert
[/TD]
[TD]Smith
[/TD]
[TD]OIG Ethics Training
[/TD]
[TD]ET2017
[/TD]
[TD]Completed
[/TD]
[TD]60
[/TD]
[TD]10/11/2017
[/TD]
[TD]2692762
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Active
[/TD]
[TD]8041
[/TD]
[TD]Acme
[/TD]
[TD]1234
[/TD]
[TD]2/4/2002
[/TD]
[TD]11/20/2017
[/TD]
[TD]Robert
[/TD]
[TD]Smith
[/TD]
[TD]OIG Ethics Training
[/TD]
[TD]ET2017
[/TD]
[TD]Completed
[/TD]
[TD]100
[/TD]
[TD]11/21/2017
[/TD]
[TD]2757346
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Active
[/TD]
[TD]8041
[/TD]
[TD]Acme
[/TD]
[TD]1234
[/TD]
[TD]2/4/2002
[/TD]
[TD]11/20/2017
[/TD]
[TD]Robert
[/TD]
[TD]Smith
[/TD]
[TD]Sexual Harassment Refresher
[/TD]
[TD]2017_SHR
[/TD]
[TD]Completed
[/TD]
[TD]70
[/TD]
[TD]10/11/2017
[/TD]
[TD]2625680
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Active
[/TD]
[TD]8041
[/TD]
[TD]Acme
[/TD]
[TD]1234
[/TD]
[TD]2/4/2002
[/TD]
[TD]11/20/2017
[/TD]
[TD]Robert
[/TD]
[TD]Smith
[/TD]
[TD]Sexual Harassment Refresher
[/TD]
[TD]2017_SHR
[/TD]
[TD]Completed
[/TD]
[TD]100
[/TD]
[TD]11/21/2017
[/TD]
[TD]2757332
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MartyS]

I couldn't get your formula to work... and not for lack of trying!

Anyways...

If you're ok with adding two extra columns (for calculations) here's a solution, providing I've understood what it is you're looking for.



Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
12342015_IRUACompleted
12342017_IRUACompleted
12342017_IRUAIn-Progress
1234ET2015Completed
1234ET2017Completed
1234ET2017Completed
12342017_SHRCompleted
12342017_SHRCompleted

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Learner Status[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Department ID[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Department[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]PSID[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Hire Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Rehire Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]First Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Last Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Course[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Course Code[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Status[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Score[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Completion Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: center"]Confirmation[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]FTPR[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]Unique Idenitifier[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]MinCalc[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Active[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8041[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Acme[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1234[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]02/04/2002[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]11/20/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Robert[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Information Resource Use Agreement[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]2015_IRUA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Completed[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]9/13/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2604534[/TD]
[TD="align: center"]1[/TD]

[TD="align: right"]2604534[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Active[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8041[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Acme[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1234[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]02/04/2002[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]11/20/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Robert[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Information Resource Use Agreement[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]2017_IRUA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Completed[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]10/11/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2646747[/TD]
[TD="align: center"]1[/TD]

[TD="align: right"]2646747[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Active[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8041[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Acme[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1234[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]02/04/2002[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]11/20/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Robert[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Information Resource Use Agreement[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]2017_IRUA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]In-Progress[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2757344[/TD]
[TD="align: center"][/TD]

[TD="align: right"]2757344[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Active[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8041[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Acme[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1234[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]02/04/2002[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]11/20/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Robert[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]OIG Ethics Training[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]ET2015[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Completed[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]9/13/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2604912[/TD]
[TD="align: center"]1[/TD]

[TD="align: right"]2604912[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Active[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8041[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Acme[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1234[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]02/04/2002[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]11/20/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Robert[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]OIG Ethics Training[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]ET2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Completed[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]60[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]10/11/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2692762[/TD]
[TD="align: center"][/TD]

[TD="align: right"]2692762[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Active[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8041[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Acme[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1234[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]02/04/2002[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]11/20/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Robert[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]OIG Ethics Training[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]ET2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Completed[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]11/21/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2757346[/TD]
[TD="align: center"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Active[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8041[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Acme[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1234[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]02/04/2002[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]11/20/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Robert[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Sexual Harassment Refresher[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]2017_SHR[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Completed[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]85[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]10/11/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2625680[/TD]
[TD="align: center"]1[/TD]

[TD="align: right"]2625680[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Active[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8041[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Acme[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1234[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]02/04/2002[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]11/20/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Robert[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Smith[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Sexual Harassment Refresher[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]2017_SHR[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Completed[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]11/21/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2757332[/TD]
[TD="align: center"][/TD]

[TD="align: right"]0[/TD]

</tbody>

Sheet12​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P2[/TH]
[TD="align: left"]=D2&J2&K2[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P3[/TH]
[TD="align: left"]=D3&J3&K3[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P4[/TH]
[TD="align: left"]=D4&J4&K4[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P5[/TH]
[TD="align: left"]=D5&J5&K5[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P6[/TH]
[TD="align: left"]=D6&J6&K6[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P7[/TH]
[TD="align: left"]=D7&J7&K7[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P8[/TH]
[TD="align: left"]=D8&J8&K8[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P9[/TH]
[TD="align: left"]=D9&J9&K9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O2[/TH]
[TD="align: left"]{=IF(AND($K2="Completed",$L2>=80,$Q2=$N2),COUNTIFS($D$2:$D$50000,$D2,$J$2:$J$50000,$J2,$L$2:$L$50000,$L2),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O3[/TH]
[TD="align: left"]{=IF(AND($K3="Completed",$L3>=80,$Q3=$N3),COUNTIFS($D$2:$D$50000,$D3,$J$2:$J$50000,$J3,$L$2:$L$50000,$L3),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O4[/TH]
[TD="align: left"]{=IF(AND($K4="Completed",$L4>=80,$Q4=$N4),COUNTIFS($D$2:$D$50000,$D4,$J$2:$J$50000,$J4,$L$2:$L$50000,$L4),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O5[/TH]
[TD="align: left"]{=IF(AND($K5="Completed",$L5>=80,$Q5=$N5),COUNTIFS($D$2:$D$50000,$D5,$J$2:$J$50000,$J5,$L$2:$L$50000,$L5),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O6[/TH]
[TD="align: left"]{=IF(AND($K6="Completed",$L6>=80,$Q6=$N6),COUNTIFS($D$2:$D$50000,$D6,$J$2:$J$50000,$J6,$L$2:$L$50000,$L6),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O7[/TH]
[TD="align: left"]{=IF(AND($K7="Completed",$L7>=80,$Q7=$N7),COUNTIFS($D$2:$D$50000,$D7,$J$2:$J$50000,$J7,$L$2:$L$50000,$L7),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O8[/TH]
[TD="align: left"]{=IF(AND($K8="Completed",$L8>=80,$Q8=$N8),COUNTIFS($D$2:$D$50000,$D8,$J$2:$J$50000,$J8,$L$2:$L$50000,$L8),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O9[/TH]
[TD="align: left"]{=IF(AND($K9="Completed",$L9>=80,$Q9=$N9),COUNTIFS($D$2:$D$50000,$D9,$J$2:$J$50000,$J9,$L$2:$L$50000,$L9),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q2[/TH]
[TD="align: left"]{=IF(MIN(IF($P$2:$P$9=P2,$N$2:$N$9))=N2,N2,0)}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q3[/TH]
[TD="align: left"]{=IF(MIN(IF($P$2:$P$9=P3,$N$2:$N$9))=N3,N3,0)}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q4[/TH]
[TD="align: left"]{=IF(MIN(IF($P$2:$P$9=P4,$N$2:$N$9))=N4,N4,0)}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q5[/TH]
[TD="align: left"]{=IF(MIN(IF($P$2:$P$9=P5,$N$2:$N$9))=N5,N5,0)}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q6[/TH]
[TD="align: left"]{=IF(MIN(IF($P$2:$P$9=P6,$N$2:$N$9))=N6,N6,0)}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q7[/TH]
[TD="align: left"]{=IF(MIN(IF($P$2:$P$9=P7,$N$2:$N$9))=N7,N7,0)}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q8[/TH]
[TD="align: left"]{=IF(MIN(IF($P$2:$P$9=P8,$N$2:$N$9))=N8,N8,0)}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q9[/TH]
[TD="align: left"]{=IF(MIN(IF($P$2:$P$9=P9,$N$2:$N$9))=N9,N9,0)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




Let me know if that's what you were looking for?!

 

[AlanY]

try this

Excel 2013/2016
	A	D	G	H	J	K	L	M	N	O
1	Learner Status	PSID	First Name	Last Name	Course Code	Status	Score	Completion Date	Confirmation	FTPR
2	Active	1234	Robert	Smith	2015_IRUA	Completed	100	13/09/2017	2604534	100
3	Active	1234	Robert	Smith	2017_IRUA	Completed	100	11/10/2017	2646747	100
4	Active	1234	Robert	Smith	2017_IRUA	In-Progress			2757344	100
5	Active	1234	Robert	Smith	ET2015	Completed	100	13/09/2017	2604912	100
6	Active	1234	Robert	Smith	ET2017	Completed	60	11/10/2017	2692762	60
7	Active	1234	Robert	Smith	ET2017	Completed	100	21/11/2017	2757346	60
8	Active	1234	Robert	Smith	2017_SHR	Completed	70	11/10/2017	2625680	70
9	Active	1234	Robert	Smith	2017_SHR	Completed	100	21/11/2017	2757332	70
Sheet1

Cell Formulas
Range	Formula
O2	{=INDEX($L$2:$L$9,MATCH(SMALL(IF($D$2:$D$9=$D2,IF($J$2:$J$9=$J2,$N$2:$N$9)),1),$N$2:$N$9,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[AlanY]

you can just add if>80, "Pass" etc