Multiple criteria search with results to display percentage - vba

that_one_girl

New Member
Joined
Mar 22, 2017
Messages
43
:confused: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

  1. Look for the staff name (column A)
  2. Look to see if that staff has completed 2 courses (Column J and Column K, L or M)
  3. If they have both J and one of the other 3, then look at their hire date.
  4. Total the number of people hired from 10/1/17-9/30/18
  5. 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?

 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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)
 
Upvote 0
I had to start working on this - So to save myself time, Idid a couple of things.


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

  1. 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)

  1. 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 – 1st Required class (everyone must have)
Column K – M – 2ndrequired 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","")



 
Upvote 0
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.
 
Last edited:
Upvote 0
START DATES
TOTAL STAFF% COMPLETE
10/1/201710/1/20181Yr %21REQUIREMENTS:2YRS or less = OTE, INTRO, 2 TECH
10/1/201510/1/20183Plus Years %3YRS + = 2YRS + INCIDENT COMMAND
10/1/201510/1/20183Plus Year Tech Course %
5+ YRS = all
*Cannot be waived***ONLY TWO INTRODUCTION COURSES REQUIRED TO BE CONSIDERED "CURRENT"***
NAMECSHO/EE IDCLASSUNITREGIONSTATE STATUSOFFICEHIRE DATETRAINING STATUSOrientation To EnforcementIntroduction to Health StandardsIntroduction to Safety StandardsIntroduction to Construction and Electrical Standards
CHURCHILL, PS0358ASEENF1CURRENTAMERICAN CANYON10/2/2017
CROCKER, JP7724SSEENF1CURRENTAMERICAN CANYONN/A=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","")COMPLETEDCOMPLETEDCOMPLETED
DAVENPORT, LT4256ASEENF1CURRENTAMERICAN CANYON10/30/2017 COMPLETED COMPLETED
DE GUZMAN, SB5240ASEENF1CURRENTAMERICAN CANYONN/A COMPLETEDCOMPLETEDCOMPLETED
DIAZ, OO1907ASEENF1CURRENTAMERICAN CANYONN/A COMPLETEDCOMPLETEDCOMPLETED
GARNER, KJ6921DMENF1CURRENTAMERICAN CANYONN/A
KERNOHAN, DO2481ASST SEENF1CURRENTAMERICAN CANYON2/5/2018 COMPLETED
KHERADPIR, SN1374ASEENF1CURRENTAMERICAN CANYONN/A COMPLETED
LESLIE, JQ6724ASEENF1CURRENTAMERICAN CANYON10/1/2016 COMPLETED COMPLETED
STEVENSON, SR4096ASEENF1CURRENTAMERICAN CANYON4/1/2016 COMPLETEDCOMPLETEDCOMPLETED
BRENES, BT7977ASST SEENF1CURRENTFOSTER CITY7/1/2016 COMPLETED COMPLETED
CARMICHAEL, JZ3164ASEENF1CURRENTFOSTER CITYN/A COMPLETEDCOMPLETEDCOMPLETEDCOMPLETED
GUIRIBA, PD9005ASEENF1CURRENTFOSTER CITYN/A PM APPROVED WAIVERCOMPLETEDCOMPLETED
KIM, BX0360DMENF1CURRENTFOSTER CITYN/A
TOLENTINO, GC8043ASEENF1CURRENTFOSTER CITYN/A COMPLETEDCOMPLETEDCOMPLETED
CHEN, WB4158ASEENF1CURRENTFREMONT5/1/2016 COMPLETED COMPLETED
EISSA, EU4975ASEENF1CURRENTFREMONT4/1/2016 COMPLETED COMPLETED
HASKELL, RW6136ASEENF1CURRENTFREMONTN/A COMPLETED COMPLETED
HUEZO, AH6984ASST SEENF1CURRENTFREMONT12/1/2016 COMPLETED COMPLETED
JACKSON, CE2672ADMENF1CURRENTFREMONTN/A COMPLETEDCOMPLETEDCOMPLETED
MAGRO, AB8457ASEENF1CURRENTFREMONTN/A COMPLETEDCOMPLETED COMPLETED
MARTIN, LP7849ASEENF1CURRENTFREMONTN/A COMPLETEDCOMPLETEDCOMPLETED
<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> </tbody>
 
Upvote 0
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?
A
B
C
D
E
F
G
H
I
J
K
L
M
1
START DATES
# Yrs
TOTAL STAFF
% COMPLETE

2
10/1/2017
10/1/2018
1
Yr %
2.5
REQUIREMENTS:
2YRS or less = OTE, INTRO, 2 TECH
3
10/1/2015
10/1/2018
3
Plus Years %
3YRS + = 2YRS + INCIDENT COMMAND
4
10/1/2015
10/1/2018
3
Yrs Tech Course %
5+ YRS = all
5
6
NAME
CSHO/EE ID
CLASS
UNIT
REGION
STATE STATUS
OFFICE
HIRE DATE
TRAINING STATUS
Orientation To Enforcement
Introduction to Health Standards
Introduction to Safety Standards
Introduction to Construction and Electrical Standards
7
CHURCHILL, P
S0358
ASE
ENF
1
CURRENT
AMERICAN CANYON
10/2/2017
<tbody> </tbody>
 
Upvote 0
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,"")
 
Upvote 0

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.

 
Upvote 0
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,"")
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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
Back
Top