Multiple criteria search with results to display percentage - vba

[that_one_girl]

I have a worksheet that searches for staff name, thenpopulates their information from a reference sheet. Then there are trainingclasses listed, and the second formula searches for their name in a trainingrecords table, and if their name and that class title is found, it populates ascompleted.

What I need to do is

2. Look for the staff name (column A)
3. Look to see if that staff has completed 2 courses (Column J and Column K, L or M)
4. If they have both J and one of the other 3, then look at their hire date.
5. Total the number of people hired from 10/1/17-9/30/18
6. Divide the number of people that were hired meet the requirements in step 3 and were hired between 10/1/17-9/30/18 to give me a percentage.

Example:

• There were 19 people with hire dates between 10/1/17 and 9/30/18
• MANZO, B (column A) was hired 10/2/17 (column H), COMPLETED Orientation to Enforcement (column J) and COMPLETED Introduction to Safety (column K). Count as 1
• (in row 222 of the attached) GIER, L (column A) was hired 10/16/17 (column H), COMPLETED Orientation to Enforcement (column J) and COMPLETED Introduction to Safety (column K). Count as 1
• Divide 2/19*100 = 10.5% COMPLETED REQUIRED TRAINING to populate in cell C2

Is there a simpler way to do this? And what is best – a VBAcode, or a formula?

 

[mrshl9898]

So if there is anything in their row in column J then either K,L or M then they meet the requirement for 2 courses?

May be off here below, you mentioned row 222 of the attached, but nothing is attached. and not certain about where C2 fits into things, and where the table lies in the sheet.

I assumed a table with headers starting in row 1.




In N2

=IF(AND(H2>43009,H2<43373,J2<>"",OR(K2<>"",L2<>"",M2<>"")),1,0)

*May be easier to have the dates in cells, and look if greater than or less than, rather than the dates values above

And for the total

=COUNTIF(N:N,1)/(COUNTA(N:N)-1)

 

[that_one_girl]

I had to start working on this - So to save myself time, Idid a couple of things.

2. I put in start dates (B2), and end dates of 1yr later (C2).
3. I also have a column that counted the total number of staff that were hired between those dates

So now,

2. I want to a formula that will just list a 1 if the criteria is all met (criteria below). This will be in it’s own cell (i9)

2. I want a formula that will total those with a 1, and divide it by the total staff in those dates (F1), multiply it by 100 and give me a % in % complete Cell (G2)

Cells:
B2 – Start Date
C2 – 1 yr forward from Startdate
F2 – Total Staff with hire datesbetween B2 and C2
Column A – Names of Staff
Column H – Hire Dates of staff
Column J – 1^st Required class (everyone must have)
Column K – M – 2^ndrequired class (everyone must have onlyone of these plus column J)
Column I – to show 1 if theymeet that criteria (column J class, 1 of the L-M classes, and H date between B2and C2)
G2 – To divide the total in F1 with the total number of 1sin column I


This is the formula I’m trying to enter in column I – but ofcourse, it’s not working……help????

F2 (shows 21)

G2 (blank, no formula yet)
INSIDE COLUMN i9 =IF(COUNTIFS('DOSHTrainingRecordsVer.03-ALrev.10.12.18.xlsm'!DTR_NAMES,$A9,$J9,"COMPLETED",AND($K9="COMPLETED",OR($L9="COMPLETED",$M9="COMPLETED")),"COMPLETE",$H9,">=B2",$H9,"<=C2"),"1","")

 

[mrshl9898]

This is getting hard to visualize, any chance you can upload a copy to dropbox or similar? Just change the names/numbers if worried about security etc.

As for that formula, i'm not sure about AND and OR in countifs, but you have criteria where you need a range to look for it in.

 

[that_one_girl]

[TABLE="width: 1705"]
<colgroup><col width="147" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5376;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"> <col width="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;"> <col width="107" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;"> <col width="182" style="width: 137pt; mso-width-source: userset; mso-width-alt: 6656;"> <col width="133" style="width: 100pt; mso-width-source: userset; mso-width-alt: 4864;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="155" style="width: 116pt; mso-width-source: userset; mso-width-alt: 5668;"> <col width="209" style="width: 157pt; mso-width-source: userset; mso-width-alt: 7643;"> <col width="327" style="width: 245pt; mso-width-source: userset; mso-width-alt: 11958;" span="3"> <tbody>[TR]
[TD="width: 147, bgcolor: transparent"][/TD]
[TD="width: 185, bgcolor: transparent, colspan: 2"]START DATES
[/TD]
[TD="width: 88, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 182, bgcolor: transparent"]TOTAL STAFF[/TD]
[TD="width: 133, bgcolor: transparent"]% COMPLETE[/TD]
[TD="width: 86, bgcolor: transparent"][/TD]
[TD="width: 155, bgcolor: transparent"][/TD]
[TD="width: 209, bgcolor: transparent"][/TD]
[TD="width: 327, bgcolor: transparent"][/TD]
[TD="width: 327, bgcolor: transparent"][/TD]
[TD="width: 327, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]10/1/2017[/TD]
[TD="bgcolor: transparent"]10/1/2018[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]Yr %[/TD]
[TD="bgcolor: transparent"]21[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"]REQUIREMENTS:[/TD]
[TD="bgcolor: yellow"]2YRS or less = OTE, INTRO, 2 TECH[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]10/1/2015[/TD]
[TD="bgcolor: transparent"]10/1/2018[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]Plus Years %[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"]3YRS + = 2YRS + INCIDENT COMMAND[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]10/1/2015[/TD]
[TD="bgcolor: transparent"]10/1/2018[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]Plus Year Tech Course %
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: yellow"]5+ YRS = all[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]*Cannot be waived[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , colspan: 3"]***ONLY TWO INTRODUCTION COURSES REQUIRED TO BE CONSIDERED "CURRENT"***[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] "]NAME[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] "]CSHO/EE ID[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] "]CLASS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] "]UNIT[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] "]REGION[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] "]STATE STATUS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] "]OFFICE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] "]HIRE DATE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] "]TRAINING STATUS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] "]Orientation To Enforcement[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] "]Introduction to Health Standards[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] "]Introduction to Safety Standards[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] "]Introduction to Construction and Electrical Standards[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CHURCHILL, P[/TD]
[TD="width: 109, bgcolor: transparent"]S0358[/TD]
[TD="width: 76, bgcolor: transparent"]ASE[/TD]
[TD="width: 88, bgcolor: transparent"]ENF[/TD]
[TD="width: 107, bgcolor: transparent"]1[/TD]
[TD="width: 182, bgcolor: transparent"]CURRENT[/TD]
[TD="width: 133, bgcolor: transparent"]AMERICAN CANYON[/TD]
[TD="width: 86, bgcolor: transparent"]10/2/2017[/TD]
[TD="width: 155, bgcolor: transparent"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CROCKER, J[/TD]
[TD="width: 109, bgcolor: transparent"]P7724[/TD]
[TD="width: 76, bgcolor: transparent"]SSE[/TD]
[TD="width: 88, bgcolor: transparent"]ENF[/TD]
[TD="width: 107, bgcolor: transparent"]1[/TD]
[TD="width: 182, bgcolor: transparent"]CURRENT[/TD]
[TD="width: 133, bgcolor: transparent"]AMERICAN CANYON[/TD]
[TD="width: 86, bgcolor: transparent"]N/A[/TD]
[TD="bgcolor: transparent"]=IF(COUNTIFS('DOSHTrainingRecordsVer.03-ALrev.10.12.18.xlsm'!DTR_NAMES,$A9,$J9,"COMPLETED",AND($K9="COMPLETED",OR($L9="COMPLETED",$M9="COMPLETED")),"COMPLETE",$H9,">=ONE_YR_GOAL",$H9,"<=C2"),"1","")[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DAVENPORT, L[/TD]
[TD="bgcolor: transparent"]T4256[/TD]
[TD="bgcolor: transparent"]ASE[/TD]
[TD="bgcolor: transparent"]ENF[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]AMERICAN CANYON[/TD]
[TD="bgcolor: transparent"]10/30/2017[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DE GUZMAN, S[/TD]
[TD="width: 109, bgcolor: transparent"]B5240[/TD]
[TD="width: 76, bgcolor: transparent"]ASE[/TD]
[TD="width: 88, bgcolor: transparent"]ENF[/TD]
[TD="width: 107, bgcolor: transparent"]1[/TD]
[TD="width: 182, bgcolor: transparent"]CURRENT[/TD]
[TD="width: 133, bgcolor: transparent"]AMERICAN CANYON[/TD]
[TD="width: 86, bgcolor: transparent"]N/A[/TD]
[TD="width: 155, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DIAZ, O[/TD]
[TD="bgcolor: transparent"]O1907[/TD]
[TD="bgcolor: transparent"]ASE[/TD]
[TD="bgcolor: transparent"]ENF[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]AMERICAN CANYON[/TD]
[TD="bgcolor: transparent"]N/A[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]GARNER, K[/TD]
[TD="bgcolor: transparent"]J6921[/TD]
[TD="bgcolor: transparent"]DM[/TD]
[TD="bgcolor: transparent"]ENF[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]AMERICAN CANYON[/TD]
[TD="bgcolor: transparent"]N/A[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]KERNOHAN, D[/TD]
[TD="width: 109, bgcolor: transparent"]O2481[/TD]
[TD="width: 76, bgcolor: transparent"]ASST SE[/TD]
[TD="width: 88, bgcolor: transparent"]ENF[/TD]
[TD="width: 107, bgcolor: transparent"]1[/TD]
[TD="width: 182, bgcolor: transparent"]CURRENT[/TD]
[TD="width: 133, bgcolor: transparent"]AMERICAN CANYON[/TD]
[TD="width: 86, bgcolor: transparent"]2/5/2018[/TD]
[TD="width: 155, bgcolor: transparent"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]KHERADPIR, S[/TD]
[TD="bgcolor: transparent"]N1374[/TD]
[TD="bgcolor: transparent"]ASE[/TD]
[TD="bgcolor: transparent"]ENF[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]AMERICAN CANYON[/TD]
[TD="bgcolor: transparent"]N/A[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]LESLIE, J[/TD]
[TD="bgcolor: transparent"]Q6724[/TD]
[TD="bgcolor: transparent"]ASE[/TD]
[TD="bgcolor: transparent"]ENF[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]AMERICAN CANYON[/TD]
[TD="bgcolor: transparent"]10/1/2016[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]STEVENSON, S[/TD]
[TD="width: 109, bgcolor: transparent"]R4096[/TD]
[TD="width: 76, bgcolor: transparent"]ASE[/TD]
[TD="width: 88, bgcolor: transparent"]ENF[/TD]
[TD="width: 107, bgcolor: transparent"]1[/TD]
[TD="width: 182, bgcolor: transparent"]CURRENT[/TD]
[TD="width: 133, bgcolor: transparent"]AMERICAN CANYON[/TD]
[TD="width: 86, bgcolor: transparent"]4/1/2016[/TD]
[TD="width: 155, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]BRENES, B[/TD]
[TD="bgcolor: transparent"]T7977[/TD]
[TD="bgcolor: transparent"]ASST SE[/TD]
[TD="bgcolor: transparent"]ENF[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]FOSTER CITY[/TD]
[TD="bgcolor: transparent"]7/1/2016[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CARMICHAEL, J[/TD]
[TD="bgcolor: transparent"]Z3164[/TD]
[TD="bgcolor: transparent"]ASE[/TD]
[TD="bgcolor: transparent"]ENF[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]FOSTER CITY[/TD]
[TD="bgcolor: transparent"]N/A[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]GUIRIBA, P[/TD]
[TD="width: 109, bgcolor: transparent"]D9005[/TD]
[TD="width: 76, bgcolor: transparent"]ASE[/TD]
[TD="width: 88, bgcolor: transparent"]ENF[/TD]
[TD="width: 107, bgcolor: transparent"]1[/TD]
[TD="width: 182, bgcolor: transparent"]CURRENT[/TD]
[TD="width: 133, bgcolor: transparent"]FOSTER CITY[/TD]
[TD="width: 86, bgcolor: transparent"]N/A[/TD]
[TD="width: 155, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]PM APPROVED WAIVER[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]KIM, B[/TD]
[TD="width: 109, bgcolor: transparent"]X0360[/TD]
[TD="width: 76, bgcolor: transparent"]DM[/TD]
[TD="width: 88, bgcolor: transparent"]ENF[/TD]
[TD="width: 107, bgcolor: transparent"]1[/TD]
[TD="width: 182, bgcolor: transparent"]CURRENT[/TD]
[TD="width: 133, bgcolor: transparent"]FOSTER CITY[/TD]
[TD="width: 86, bgcolor: transparent"]N/A[/TD]
[TD="width: 155, bgcolor: transparent"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TOLENTINO, G[/TD]
[TD="bgcolor: transparent"]C8043[/TD]
[TD="bgcolor: transparent"]ASE[/TD]
[TD="bgcolor: transparent"]ENF[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]FOSTER CITY[/TD]
[TD="bgcolor: transparent"]N/A[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CHEN, W[/TD]
[TD="width: 109, bgcolor: transparent"]B4158[/TD]
[TD="width: 76, bgcolor: transparent"]ASE[/TD]
[TD="width: 88, bgcolor: transparent"]ENF[/TD]
[TD="width: 107, bgcolor: transparent"]1[/TD]
[TD="width: 182, bgcolor: transparent"]CURRENT[/TD]
[TD="width: 133, bgcolor: transparent"]FREMONT[/TD]
[TD="width: 86, bgcolor: transparent"]5/1/2016[/TD]
[TD="width: 155, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]EISSA, E[/TD]
[TD="bgcolor: transparent"]U4975[/TD]
[TD="bgcolor: transparent"]ASE[/TD]
[TD="bgcolor: transparent"]ENF[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]FREMONT[/TD]
[TD="bgcolor: transparent"]4/1/2016[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]HASKELL, R[/TD]
[TD="bgcolor: transparent"]W6136[/TD]
[TD="bgcolor: transparent"]ASE[/TD]
[TD="bgcolor: transparent"]ENF[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]FREMONT[/TD]
[TD="bgcolor: transparent"]N/A[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]HUEZO, A[/TD]
[TD="bgcolor: transparent"]H6984[/TD]
[TD="bgcolor: transparent"]ASST SE[/TD]
[TD="bgcolor: transparent"]ENF[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]FREMONT[/TD]
[TD="bgcolor: transparent"]12/1/2016[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]JACKSON, C[/TD]
[TD="width: 109, bgcolor: transparent"]E2672[/TD]
[TD="width: 76, bgcolor: transparent"]ADM[/TD]
[TD="width: 88, bgcolor: transparent"]ENF[/TD]
[TD="width: 107, bgcolor: transparent"]1[/TD]
[TD="width: 182, bgcolor: transparent"]CURRENT[/TD]
[TD="width: 133, bgcolor: transparent"]FREMONT[/TD]
[TD="width: 86, bgcolor: transparent"]N/A[/TD]
[TD="width: 155, bgcolor: transparent"] [/TD]
[TD] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MAGRO, A[/TD]
[TD="width: 109, bgcolor: transparent"]B8457[/TD]
[TD="width: 76, bgcolor: transparent"]ASE[/TD]
[TD="width: 88, bgcolor: transparent"]ENF[/TD]
[TD="width: 107, bgcolor: transparent"]1[/TD]
[TD="width: 182, bgcolor: transparent"]CURRENT[/TD]
[TD="width: 133, bgcolor: transparent"]FREMONT[/TD]
[TD="width: 86, bgcolor: transparent"]N/A[/TD]
[TD="width: 155, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MARTIN, L[/TD]
[TD="width: 109, bgcolor: transparent"]P7849[/TD]
[TD="width: 76, bgcolor: transparent"]ASE[/TD]
[TD="width: 88, bgcolor: transparent"]ENF[/TD]
[TD="width: 107, bgcolor: transparent"]1[/TD]
[TD="width: 182, bgcolor: transparent"]CURRENT[/TD]
[TD="width: 133, bgcolor: transparent"]FREMONT[/TD]
[TD="width: 86, bgcolor: transparent"]N/A[/TD]
[TD="width: 155, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD="bgcolor: transparent"]COMPLETED[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[that_one_girl]

This is getting hard to visualize, any chance you can upload a copy to dropbox or similar? Just change the names/numbers if worried about security etc.

As for that formula, i'm not sure about AND and OR in countifs, but you have criteria where you need a range to look for it in.

I cannot, work computer blocks me. Does this help?

<tbody> [TD="width: 22, bgcolor: transparent"][/TD]
[TD="width: 85, bgcolor: transparent"]

A​

[/TD]
[TD="width: 90, bgcolor: transparent"]

B​

[/TD]
[TD="width: 84, bgcolor: transparent"]

C ​

[/TD]
[TD="width: 48, bgcolor: transparent"]

D​

[/TD]
[TD="width: 84, bgcolor: transparent"]

E​

[/TD]
[TD="width: 84, bgcolor: transparent"]

F​

[/TD]
[TD="width: 80, bgcolor: transparent"]

G​

[/TD]
[TD="width: 84, bgcolor: transparent"]

H ​

[/TD]
[TD="width: 78, bgcolor: transparent"]

I​

[/TD]
[TD="width: 117, bgcolor: transparent"]

J​

[/TD]
[TD="width: 91, bgcolor: transparent"]

K​

[/TD]
[TD="width: 91, bgcolor: transparent"]

L​

[/TD]
[TD="width: 92, bgcolor: transparent"]

M​

[/TD]
[TD="width: 22, bgcolor: transparent"]

1​

[/TD]
[TD="width: 85, bgcolor: transparent"][/TD]
[TD="width: 90, bgcolor: transparent"]

START DATES​

[/TD]
[TD="width: 84, bgcolor: transparent"]

# Yrs​

[/TD]
[TD="width: 48, bgcolor: transparent"][/TD]
[TD="width: 84, bgcolor: transparent"]

​

[/TD]
[TD="width: 84, bgcolor: transparent"]

TOTAL STAFF​

[/TD]
[TD="width: 80, bgcolor: transparent"]

% COMPLETE​

[/TD]
[TD="width: 84, bgcolor: transparent"][/TD]
[TD="width: 78, bgcolor: transparent"][/TD]
[TD="width: 117, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[TD="width: 48, bgcolor: transparent"]
[/TD]
[TD="width: 22, bgcolor: transparent"]

2​

[/TD]
[TD="width: 85, bgcolor: transparent"][/TD]
[TD="width: 90, bgcolor: transparent"]

10/1/2017​

[/TD]
[TD="width: 84, bgcolor: transparent"]

10/1/2018​

[/TD]
[TD="width: 48, bgcolor: transparent"]

1​

[/TD]
[TD="width: 84, bgcolor: transparent"]

Yr %​

[/TD]
[TD="width: 84, bgcolor: transparent"]

2.5​

[/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 84, bgcolor: transparent"][/TD]
[TD="width: 78, bgcolor: transparent"][/TD]
[TD="width: 117"]

REQUIREMENTS:​

[/TD]
[TD="width: 91"]

2YRS or less = OTE, INTRO, 2 TECH​

[/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 22, bgcolor: transparent"]

3​

[/TD]
[TD="width: 85, bgcolor: transparent"][/TD]
[TD="width: 90, bgcolor: transparent"]

10/1/2015​

[/TD]
[TD="width: 84, bgcolor: transparent"]

10/1/2018​

[/TD]
[TD="width: 48, bgcolor: transparent"]

3​

[/TD]
[TD="width: 84, bgcolor: transparent"]

Plus Years %​

[/TD]
[TD="width: 84, bgcolor: transparent"][/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 84, bgcolor: transparent"][/TD]
[TD="width: 78, bgcolor: transparent"][/TD]
[TD="width: 117, bgcolor: transparent"][/TD]
[TD="width: 91"]

3YRS + = 2YRS + INCIDENT COMMAND​

[/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 22, bgcolor: transparent"]

4​

[/TD]
[TD="width: 85, bgcolor: transparent"][/TD]
[TD="width: 90, bgcolor: transparent"]

10/1/2015​

[/TD]
[TD="width: 84, bgcolor: transparent"]

10/1/2018​

[/TD]
[TD="width: 48, bgcolor: transparent"]

3​

[/TD]
[TD="width: 84, bgcolor: transparent"]

Yrs Tech Course %​

[/TD]
[TD="width: 84, bgcolor: transparent"][/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 84, bgcolor: transparent"][/TD]
[TD="width: 78, bgcolor: transparent"][/TD]
[TD="width: 117, bgcolor: transparent"][/TD]
[TD="width: 91"]

5+ YRS = all​

[/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 22, bgcolor: transparent"]

5​

[/TD]
[TD="width: 85, bgcolor: transparent"][/TD]
[TD="width: 90, bgcolor: transparent"]

​

[/TD]
[TD="width: 84, bgcolor: transparent"]

​

[/TD]
[TD="width: 48, bgcolor: transparent"][/TD]
[TD="width: 84, bgcolor: transparent"][/TD]
[TD="width: 84, bgcolor: transparent"][/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 84, bgcolor: transparent"][/TD]
[TD="width: 78, bgcolor: transparent"][/TD]
[TD="width: 117, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[TD="width: 91, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 22, bgcolor: transparent"]

6​

[/TD]
[TD="width: 85"]

NAME​

[/TD]
[TD="width: 90"]

CSHO/EE ID​

[/TD]
[TD="width: 84"]

CLASS​

[/TD]
[TD="width: 48"]

UNIT​

[/TD]
[TD="width: 84"]

REGION​

[/TD]
[TD="width: 84"]

STATE STATUS​

[/TD]
[TD="width: 80"]

OFFICE​

[/TD]
[TD="width: 84"]

HIRE DATE​

[/TD]
[TD="width: 78"]

TRAINING STATUS​

[/TD]
[TD="width: 117"]

Orientation To Enforcement​

[/TD]
[TD="width: 91"]

Introduction to Health Standards​

[/TD]
[TD="width: 91"]

Introduction to Safety Standards​

[/TD]
[TD="width: 92"]

Introduction to Construction and Electrical Standards​

[/TD]
[TD="width: 22, bgcolor: transparent"]

7​

[/TD]
[TD="width: 85, bgcolor: transparent"]

CHURCHILL, P​

[/TD]
[TD="width: 90, bgcolor: transparent"]

S0358​

[/TD]
[TD="width: 84, bgcolor: transparent"]

ASE​

[/TD]
[TD="width: 48, bgcolor: transparent"]

ENF​

[/TD]
[TD="width: 84, bgcolor: transparent"]

1​

[/TD]
[TD="width: 84, bgcolor: transparent"]

CURRENT​

[/TD]
[TD="width: 80, bgcolor: transparent"]

AMERICAN CANYON​

[/TD]
[TD="width: 84, bgcolor: transparent"]

10/2/2017​

[/TD]
[TD="width: 78, bgcolor: transparent"]

​

[/TD]
[TD="width: 117"]

​

[/TD]
[TD="width: 91"]

​

[/TD]
[TD="width: 91"]

​

[/TD]
[TD="width: 92"]

​

[/TD]
</tbody>

 

[mrshl9898]

Not sure what this is,

COUNTIFS('DOSHTrainingRecordsVer.03-ALrev.10.12.18.xlsm'!DTR_NAMES,$A9

But this is working, perhaps you can add to it?

=IF(AND($J9="COMPLETED",OR($K9="COMPLETED",$L9="COMPLETED",$M9="COMPLETED"),$H9>=$B$2,$H9<=$C$2),1,"")

 

[that_one_girl]

Thank you! I’ll try it!
That is another work book that holds all of the trainingrecords. It looks in that workbook for the name of the staff member.

 

[mrshl9898]

COUNTIFS won't return a name though, and it's the incorrect syntax for COUNTIFS. There's also no range, and the refernce looks off.


This will look at a cell in the other book. Though if you're looking for a name you may need MATCH or LOOKUP depending what you are trying to achieve.


=IF(AND('[DOSHTrainingRecordsVer.03-ALrev.10.12.18.xlsm]DTR_NAMES'!$H$8=$A9,$J9="COMPLETED",OR($K9="COMPLETED",$L9="COMPLETED",$M9="COMPLETED"),$H9>=$B$2,$H9<=$C$2),1,"")