Forum Discussion
Please Help with complex conditional formula to determine headcounts per each month
Dear Team,
I find myself in a tight corner with excel today.
My GOAL: to be able to calculate the headcount (HC) in the company on a monthly basis. My intention for area highlighted blue with the different months in the attached testsheet should yield "1"s and "0"s only. "1" will mean that person counts as 1 headcount for that month and "0" will mean that staff doesn't count as a headcount.
To count Headcounts will depend on the following conditions:
1. An employees contract startdate and enddate should by default define the period for which we have this headcount BUT:
a) a contract could end BEFORE the contract enddate(check column G for the actual Termination Date). In this case the headcount period should be between the contract startdate and the actual termination date.
b) a BLANK cell for column K means the contract is UNLIMITED/PERMANENT.
2. In the HC formula kindly EXCLUDE(do not count) staff with the "not EHQ HC" or "EXTERNAL" HC type in column I.
3. I will be dealing with at least 8years data and thus will be great if the formula in cell N2 could simply be dragged across to all the other cells without 'breaking' the report.
Thanks.
NoviceKay
In N2:
=($J2<EOMONTH(N$1,0))*(IF($G2<>"",$G2,IF($K2<>"",$K2,DATE(2099,12,31)))>=N$1)*($I2<>"not EHQ HC")*($I2<>"EXTERNAL")
Fill down, then to the right, or vice versa.
8 Replies
In N2:
=($J2<EOMONTH(N$1,0))*(IF($G2<>"",$G2,IF($K2<>"",$K2,DATE(2099,12,31)))>=N$1)*($I2<>"not EHQ HC")*($I2<>"EXTERNAL")
Fill down, then to the right, or vice versa.
Hello Hans Vogelaar,
Many thanks for your feedback and it indeed was PRECISELY what I needed. Thanks so much.
I have used it successfully and in the sheet "HR_Dashboard Report" in the updated attached file, it works completely fine.
I have tried to create a pivot table with it but what I am noticing is that if I double-click the grand total value amount Excel spits out all source data rows, completely disregarding the filter setting.
For example, looking at the pivot table on the "Pivottable" sheet, when i double click on cell B13 it should normally show me only 6 rows but this is not the case(with headers). Any idea what I could be doing wrong here?
Thanks.
NoviceKayI'm afraid that's how pivot tables work - all rows contribute to a total, even if that contribution is 0...
