Forum Discussion

NoviceKB's avatar
NoviceKB
Brass Contributor
May 24, 2022
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...
excel
  • HansVogelaar's avatar
    HansVogelaar
    May 24, 2022

    NoviceKB 

    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.

HansVogelaar's avatar
HansVogelaar
MVP
May 25, 2022

NoviceKB 

I'm afraid that's how pivot tables work - all rows contribute to a total, even if that contribution is 0...

NoviceKB's avatar
NoviceKB
Brass Contributor
May 25, 2022
I understand what you mean but I know from previous pivot tables(for other reports) that normally when I click on the number in the pivot table it will show me EXACTLY the data the number is representing. For instance if I click on a 2, it will open a new sheet with 2 rows of my original data and this is what I am not getting with this exact pivot table.

Hope you got my point HansVogelaar. Sorry for the bother 🙂
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 25, 2022

    NoviceKB 

    You will get fewer rows if you double-click one of the numbers in the interior of the pivot table.

    • NoviceKB's avatar
      NoviceKB
      Brass Contributor
      May 25, 2022

      You are right. When I click on cell B4 in the pivot table for "Black" Department (Jan2020), i would expect it to show me just 2 rows but it shows 4 rows including the row for Miss Rene Bruen who started in October 2021.

      I struggle to see how that row contributed to the number 2 in cell B4.

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        May 25, 2022

        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.

Resources