Calculations for HR KPI's, specifically around headcount (historical and current).

maxim642

Board Regular
Joined
Feb 4, 2021
Messages
69
Office Version
  1. 365
Platform
  1. MacOS
Below is a sample of my dataset. This is on the first tab.

HR Dashboard - Anonymous.xlsx
ABCDEFGHI
1Employee #GenderCountryHire DateTermination DateEmployment StatusLocationDirectorateLevel
21283FemaleViet Nam6/1/179/30/20TerminatedHo Chi MinhCCO
31366MaleNigeria8/4/10Full-Time on Permanent ContractLagosCCOD
41328MaleArmenia4/4/16Full-Time on Permanent ContractYerevanCAOE
51289MaleChile6/12/178/14/20TerminatedSantiagoCCO
61321FemaleArmenia8/1/16Full-Time on Permanent ContractYerevanCFOD
7529MaleNigeria8/6/20Full-Time on Permanent ContractLagosCAOE
81367MaleNigeria1/3/11Full-Time on Permanent ContractLagosCTOE
9479MaleKenya6/1/20Full-Time on Permanent ContractNairobiCCOE
10788MaleArmenia4/22/21Full-Time on Permanent ContractYerevanCOOF
11663MaleArmenia2/1/21Full-Time on Permanent ContractYerevanCOOG
121322FemaleArmenia7/15/196/16/20TerminatedYerevanCCRO
131332MaleArmenia7/17/18Full-Time on Permanent ContractYerevanCTOF
141357FemaleArmenia8/8/17Full-Time on Permanent ContractYerevanCTOG
15181FemaleArmenia2/10/20Full-Time on Permanent ContractYerevanCEOF
161327MaleArmenia12/20/17Full-Time on Permanent ContractYerevanCCROE
171338MaleArmenia9/18/18Full-Time on Permanent ContractYerevanCTOG
18761FemaleArmenia3/29/21Full-Time on Permanent ContractYerevanCOOG
191410MaleNigeria7/15/16Full-Time on Permanent ContractAbujaCCOF
201384FemaleNigeria5/1/18Full-Time on Permanent ContractLagosCFOF
211364MaleArmenia9/3/18Full-Time on Permanent ContractYerevanCCROE
221316FemaleArmenia4/22/19Full-Time on Permanent ContractYerevanCCROE
231345MaleArmenia2/1/19Full-Time on Permanent ContractYerevanCCROF
241352FemaleArmenia5/21/18Full-Time on Permanent ContractYerevanCCROF
25321MaleRussian Federation11/1/06Full-Time on Permanent ContractMoscowCISOA
261329MaleArmenia10/10/19Full-time on Fixed-term ContractYerevanCCROG
27539FemaleArmenia9/2/20Full-Time on Permanent ContractYerevanCCOE
281358FemaleArmenia7/3/17Full-Time on Permanent ContractYerevanCDE
291290MaleCyprus9/5/189/30/20TerminatedSantiagoCCO
301324FemaleArmenia5/27/19Full-Time on Permanent ContractYerevanCCROG
311331FemaleArmenia7/11/19Full-Time on Permanent ContractYerevanCCROG
321360FemaleArmenia9/12/19Full-Time on Permanent ContractYerevanCCROG
331334MaleArmenia8/16/18Full-time on Fixed-term ContractYerevanCCROF
341351FemaleArmenia10/2/17Full-time on Fixed-term ContractYerevanCCROF
351344FemaleArmenia5/10/17Full-Time on Permanent ContractYerevanCDG
361404MaleNigeria5/12/14Full-Time on Permanent ContractAbujaCTOF
371339FemaleArmenia6/13/16Full-Time on Permanent ContractYerevanCCROF
381355FemaleArmenia8/1/16Full-Time on Permanent ContractYerevanCOOG
39526FemaleArmenia8/4/20Full-Time on Permanent ContractYerevanCCROG
40674MaleArmenia2/15/21Full-time on Fixed-term ContractYerevanCCROG
41473FemaleArmenia6/1/20Full-Time on Permanent ContractYerevanCCROG
42525FemaleArmenia8/3/20Full-Time on Permanent ContractYerevanCCROG
43656FemaleArmenia1/25/21Full-Time on Permanent ContractYerevanCCROG
441478MaleArmenia3/2/20Full-time on Fixed-term ContractYerevanCCROG
45530MaleArmenia8/12/20Full-Time on Permanent ContractYerevanCCROG
461464MaleArmenia1/13/20Full-Time on Permanent ContractYerevanCCROG
47683FemaleArmenia3/1/21Full-Time on Permanent ContractYerevanCCROG
48675MaleArmenia2/15/21Full-time on Fixed-term ContractYerevanCCROG
49531FemaleArmenia8/12/20Full-Time on Permanent ContractYerevanCCROG
50511FemaleArmenia7/23/20Full-Time on Permanent ContractYerevanCCROG
51595MaleLebanon11/16/20Full-time on Fixed-term ContractYerevanCCROG
521452FemaleArmenia12/18/19Full-Time on Permanent ContractYerevanCCROG
53503FemaleArmenia7/15/20Full-Time on Permanent ContractYerevanCCROG
54532MaleArmenia8/12/20Full-Time on Permanent ContractYerevanCCROG
551343FemaleArmenia11/4/19Full-Time on Permanent ContractYerevanCCROG
561340FemaleArmenia8/19/19Full-Time on Permanent ContractYerevanCCROG
571333MaleArmenia10/1/19Full-Time on Permanent ContractYerevanCCROG
58566MaleArmenia9/22/20Full-Time on Permanent ContractYerevanCCROG
59198MaleArmenia4/10/20Full-Time on Permanent ContractYerevanCCROG
601335MaleArmenia9/5/19Full-Time on Permanent ContractYerevanCCROG
61431FemaleArmenia4/16/20Full-time on Fixed-term ContractYerevanCCROG
62505FemaleArmenia7/15/20Full-Time on Permanent ContractYerevanCCROG
63426FemaleArmenia4/10/20Full-Time on Permanent ContractYerevanCCROG
641314FemaleArmenia5/27/19Full-Time on Permanent ContractYerevanCCROG
651350FemaleArmenia5/17/1711/30/20TerminatedYerevanCD
661375MaleNigeria10/2/12Full-Time on Permanent ContractLagosCCROE
671393MaleNigeria9/10/18Full-Time on Permanent ContractLagosCTOF
681432MaleArmenia12/2/19Full-time on Fixed-term ContractYerevanCCROG
691346FemaleArmenia10/3/17Full-Time on Permanent ContractYerevanCCROF
701330MaleArmenia1/17/187/17/20TerminatedYerevanCD
711368FemaleNigeria9/5/09Full-Time on Permanent ContractLagosCCROE
721319MaleArmenia1/14/19Full-time on Fixed-term ContractYerevanCCROG
731354MaleArmenia8/5/19Full-Time on Permanent ContractYerevanCCROG
74514MaleArmenia7/1/20Full-time on Fixed-term ContractYerevanCCROG
751349MaleArmenia4/3/17Full-Time on Permanent ContractYerevanCCROG
76653MaleArmenia1/18/211/29/21TerminatedYerevanCCRO
77712MaleArmenia3/15/21Full-Time on Permanent ContractYerevanCCROG
781313FemaleArmenia9/13/18Full-Time on Permanent ContractYerevanCCROG
791336FemaleArmenia8/2/18Full-Time on Permanent ContractYerevanCCROG
801342MaleArmenia3/18/19Full-Time on Permanent ContractYerevanCCROG
811470FemaleArmenia2/5/20Full-Time on Permanent ContractYerevanCCROG
821318MaleArmenia4/22/193/24/21TerminatedYerevanCCROG
831362FemaleArmenia7/15/191/21/21TerminatedYerevanCCRO
841323MaleArmenia9/23/19Full-time on Fixed-term ContractYerevanCCROG
85545FemaleArmenia9/1/20Full-Time on Permanent ContractYerevanCDEntry Level
861383FemaleNigeria2/2/18Full-Time on Permanent ContractLagosCCROE
871312FemaleArmenia4/1/19Full-Time on Permanent ContractYerevanCDG
88703MaleRussian Federation12/21/15Full-Time on Permanent ContractMoscowCISOB
891377MaleNigeria8/8/16Full-Time on Permanent ContractLagosCCOG
90592FemaleNigeria11/3/20Full-Time on Permanent ContractLagosCEOF
911356FemaleArmenia6/6/18Full-Time on Permanent ContractYerevanCFOF
921326FemaleArmenia10/1/19Full-Time on Permanent ContractYerevanCDG
93587FemaleArmenia11/2/20Full-Time on Permanent ContractYerevanCEOF
941359FemaleArmenia6/7/197/31/20TerminatedYerevanCD
951400FemaleNigeria3/11/19Full-Time on Permanent ContractLagosCFOG
96645MaleKenya1/11/21Full-Time on Permanent ContractNairobiCOOF
971337FemaleArmenia7/18/18Full-Time on Permanent ContractYerevanCCROG
981365MaleArmenia11/12/19Full-Time on Permanent ContractYerevanCCROG
99557MaleArmenia9/22/2010/19/20TerminatedYerevanCCRO
100571FemaleArmenia9/28/20Full-Time on Permanent ContractYerevanCCROG
101778FemaleArmenia4/15/21Full-Time on Permanent ContractYerevanCCROF
102481FemaleArmenia6/8/20Full-Time on Permanent ContractYerevanCCROG
103455FemaleArmenia5/11/20Full-Time on Permanent ContractYerevanCCROG
1041422MaleArmenia11/25/19Full-Time on Permanent ContractYerevanCCROG
1051325MaleArmenia6/17/19Full-Time on Permanent ContractYerevanCCROG
1061347FemaleArmenia7/22/19Full-Time on Permanent ContractYerevanCCROG
1071341FemaleArmenia9/1/17Full-Time on Permanent ContractYerevanCCROG
1081471MaleArmenia2/5/20Full-time on Fixed-term ContractYerevanCCROG
1091320FemaleArmenia10/7/19Full-Time on Permanent ContractYerevanCCROG
1101423FemaleArmenia11/25/1912/30/20TerminatedYerevanCCRO
1111363FemaleArmenia8/26/198/14/20TerminatedYerevanCD
112546FemaleArmenia9/1/20Full-Time on Permanent ContractYerevanCDG
113614MaleArmenia12/1/20Provision of Services AgreementYerevanCCROF
114614MaleArmenia12/1/20Provision of Services AgreementYerevanCCROF
115496FemaleArmenia7/6/201/8/21TerminatedYerevanCOO
1161376MaleNigeria4/25/162/5/21TerminatedAbujaCCOF
117794MaleRussian Federation4/26/21Professional Service Provider AgreementMoscowCTOE
1181369FemaleNigeria1/10/11Full-Time on Permanent ContractLagosCCROG
1191483MaleArmenia3/11/207/13/20TerminatedYerevanCD
1201361FemaleArmenia8/8/18Full-Time on Permanent ContractYerevanCCROG
1211315FemaleArmenia8/20/19Full-Time on Permanent ContractYerevanCCROG
122490FemaleArmenia7/1/20Full-Time on Permanent ContractYerevanCCROG
123762FemaleArmenia4/1/21Full-Time on Permanent ContractYerevanCCROG
124446FemaleArmenia5/8/20Full-Time on Permanent ContractYerevanCCROG
1251317MaleArmenia8/8/19Full-Time on Permanent ContractYerevanCCROG
1261348FemaleArmenia4/1/194/27/20TerminatedYerevanCCRO
127689FemaleArmenia3/9/21Full-Time on Permanent ContractYerevanCCROG
128633FemaleArmenia12/15/20Full-Time on Permanent ContractYerevanCCROG
1291391FemaleNigeria8/1/18Full-Time on Permanent ContractAbujaCCROF
130210MaleRussian Federation9/1/10Full-Time on Permanent ContractMoscowCTOC
131410MaleRussian Federation4/12/10Full-Time on Permanent ContractMoscowCTOD
1321419MaleNigeria11/6/19Full-Time on Permanent ContractLagosCCROG
133626MaleKenya12/7/20Full-Time on Permanent ContractNairobiCOOE
134647MaleArmenia1/18/21Full-Time on Permanent ContractYerevanCCROG
135782FemaleArmenia4/15/21Full-Time on Permanent ContractYerevanCCROG
136775MaleArmenia4/12/21Full-Time on Permanent ContractYerevanCCROG
137783MaleArmenia4/15/21Full-Time on Permanent ContractYerevanCCROF
1381353MaleArmenia7/26/198/31/20TerminatedYerevanCCRO
139377MaleRussian Federation9/14/07Full-Time on Permanent ContractMoscowCISOD
140692MaleArmenia3/4/21Full-Time on Permanent ContractYerevanCDEntry Level
141768FemaleArmenia4/5/21Full-Time on Permanent ContractYerevanCDEntry Level
142550FemaleArmenia9/7/20Full-Time on Permanent ContractYerevanCCROEntry Level
143797FemaleArmenia5/6/21Full-Time on Permanent ContractYerevanCCROEntry Level
144551MaleArmenia9/7/20Full-Time on Permanent ContractYerevanCCROEntry Level
145567FemaleArmenia10/1/20Full-Time on Permanent ContractYerevanCCROEntry Level
146774FemaleArmenia4/12/21Full-Time on Permanent ContractYerevanCCROEntry Level
147754FemaleArmenia3/22/21Full-Time on Permanent ContractYerevanCCROEntry Level
148738FemaleArmenia3/17/215/20/21TerminatedYerevanCCROEntry Level
1491371MaleCyprus11/16/095/4/20TerminatedLagosCCO
1501388MaleNigeria7/19/1810/13/20TerminatedPort HarcourtCCO
1511372MaleNigeria11/19/11Full-Time on Permanent ContractLagosCCOE
1521403MaleNigeria10/23/19Full-Time on Permanent ContractLagosCCROF
1531381FemaleNigeria11/13/17Full-Time on Permanent ContractLagosCCROF
1541418FemaleNigeria11/6/19Full-Time on Permanent ContractLagosCCROF
1551413FemaleNigeria6/7/17Full-Time on Permanent ContractLagosCCROF
1561407MaleNigeria10/14/15Full-Time on Permanent ContractAbujaCCROF
1571374FemaleNigeria2/3/14Full-Time on Permanent ContractLagosCCROF
1581415MaleNigeria5/3/18Full-Time on Permanent ContractLagosCCROF
1591412FemaleNigeria5/25/17Full-Time on Permanent ContractLagosCCROF
1601373FemaleNigeria8/18/14Full-Time on Permanent ContractLagosCCROF
1611411FemaleNigeria1/19/17Full-Time on Permanent ContractLagosCCROF
1621414MaleNigeria10/8/18Full-Time on Permanent ContractAbujaCCROF
1631370MaleNigeria10/31/11Full-Time on Permanent ContractLagosCCROG
1641421MaleArmenia2/3/20Full-Time on Permanent ContractYerevanCFOF
165497FemaleArmenia7/15/201/8/21TerminatedYerevanCOO
1661408MaleNigeria10/12/15Full-Time on Permanent ContractLagosCCOF
167444MaleNigeria4/27/20Full-Time on Permanent ContractLagosCCROG
1681417MaleNigeria11/6/19Full-Time on Permanent ContractLagosCCROG
169623FemaleKenya12/7/206/8/21TerminatedNairobiCFOF
170699FemaleArmenia3/15/214/7/21TerminatedYerevanCCROF
171544FemaleArmenia9/1/20Full-Time on Permanent ContractYerevanCDEntry Level
172585FemaleArmenia10/26/20Full-Time on Permanent ContractYerevanCDEntry Level
173773MaleArmenia4/12/21Full-Time on Permanent ContractYerevanCCROG
174780MaleArmenia4/12/21Provision of Services AgreementYerevanCCROG
175701Male12/5/16Full-Time on Permanent ContractMoscowCISOD
176359MaleRussian Federation1/26/11Full-Time on Permanent ContractMoscowCTOD
Profile Report


Below is 2nd tab. This is where I would like my calculations to appear.

The boxes in rows 1:3 will impact the figures in both the "Headcount" & "Level" table. The blue box governs the dates. The purple box governs the directorate, gender, and location. By default, the headcount splits should show all the current heads and as you click the dropdowns (maybe to see the number of females in a specific location), the numbers in the Headcount & Level box will change accordingly.

Headcount calculation is determined as count the record if the "Hire Date" is LESS THAN OR EQUAL TO the date in the query cell ("Today's Date, Last Reporting Date, Selectable Date) AND the "Termination Date" is BLANK OR AFTER the date in the query cell. The assumption is if an employee has no Termination Date, they must still be with the company and should be included in the headcount.

I wasn't sure how to add a quick-select date for the selectable date, other than a dropdown, so I've left it blank. Maybe the free text is fine? Can this be done with COUNTIFS at all, as I can easily build those up should I need to? Any help is greatly appreciated.

HR Dashboard - Anonymous.xlsx
ABCDE
1Today's Date6/10/21DirectorateAll
2Last Reporting Date5/31/21GenderAll
3Selectable DateLocationAll
4
5
6
7
8Headcount
9
10Employment StatusTodayReporting DateSelected Date
11Terminated
12Full-Time on Permanent Contract
13Full-time on Fixed-term Contract
14Provision of Services Agreement
15Professional Service Provider Agreement
16Part-Time on Permanent Contract
17
18
19
20
21Level
22
23Entry Level
24A
25B
26C
27D
28E
29F
30G
31N/A
32Blank
Summary
Cell Formulas
RangeFormula
B1B1=TODAY()
B2B2=EOMONTH(B1,-1)
Cells with Data Validation
CellAllowCriteria
E1List='Data Validation'!$C$2:$C$11
E2List='Data Validation'!$A$2:$A$4
E3List='Data Validation'!$E$2:$E$32
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
You'd be better off using pivot tables, slicers and timelines to achieve this, which would remove the need for most of the cell formula and give more dynamics:
See if this video helps:

I watched above to to create one for an HR friend, definitely an hour well spent, don't be put off by the duration of the video or as a learning experience :)
 

maxim642

Board Regular
Joined
Feb 4, 2021
Messages
69
Office Version
  1. 365
Platform
  1. MacOS
You'd be better off using pivot tables, slicers and timelines to achieve this, which would remove the need for most of the cell formula and give more dynamics:
See if this video helps:

I watched above to to create one for an HR friend, definitely an hour well spent, don't be put off by the duration of the video or as a learning experience :)
I've used pivot tables before, but thought as this calculation requires multiple criteria to count that perhaps a pivot couldn't do that, I'll check it out though, thanks.
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,890
Office Version
  1. 365
Platform
  1. Windows
Pivot tables aggregate data, i.e. SUMIF. The timeline can control the date ranges for the resulting aggregations, and slicers for different gender options etc.

Your source data looks like it's in normalised form already so it's in a great state to pivot and then do the groupings you need :)
 

Forum statistics

Threads
1,147,748
Messages
5,742,978
Members
423,769
Latest member
LongToast

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