Forum Discussion
Solved
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...
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.
NoviceKB The problem lies in the fact that the data isn't correctly structured to create a pivot table from. The attached file contains a Power Query (PQ) solution that flattens the data so that you can create a proper pivot table from it.
I'm usually using PQ on a PC, but this time I used a Mac as PQ is also on the Mac for Insiders. Though, it's not exactly the same. That's why my queries look a bit "different".
But the pivot table in the attached file does what you have in mind.
Thanks so much for your solution. It really did do the trick to have it in the PQ flattened out approach. That was the pivot table I was looking for. Much appreciated Riny van Eekelen!