Pivot Table Unique Headcount Counting Issue (Not Able To Get Headcount # Right)

%3CLINGO-SUB%20id%3D%22lingo-sub-1778931%22%20slang%3D%22en-US%22%3EPivot%20Table%20Unique%20Headcount%20Counting%20Issue%20(Not%20Able%20To%20Get%20Headcount%20%23%20Right)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1778931%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EMy%20goal%20is%20to%20uniquely%20find%20a%20headcount%20and%20movement%20(employees%20join%20or%20leave)%20per%20month%20and%20put%20this%20in%20a%20Pivot%20Table.%20My%20data%20is%20formatted%20like%20it%20is%20in%20the%20Table.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EMy%20issue%20is%20when%20I%20do%20a%20count%20of%20Employee%20number%20(Empl%20%23)%20and%20based%20on%20a%20filter%20of%20monthly%20salary%2C%20I%20don%E2%80%99t%20get%20a%20correct%20count%20when%20the%20employees%20join%20and%2For%20leave%20and%20get%20a%20constant%20headcount%20in%20the%20Pivot%20Table%20of%203%20when%20it%20should%20be%202%2C%203%2C%202%20for%20Jan%2C%20Feb%2C%20Mar.%20%3C%2FSPAN%3E%3C%2FP%3E%3COL%3E%3CLI%3E%3CSPAN%3EHow%20to%20do%20I%20fix%20this%3F%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EWhat%20is%20the%20logic%20to%20approach%20this%3F%20How%20would%20someone%20look%20at%20this%20and%20pick%20what%20method%20to%20fix%20this%3F%20This%20is%20more%20of%20a%20how%20did%20you%20get%20to%20your%20answer%20sorta%20question.%20Do%20you%20write%20down%2Fthink%20how%20and%20what%20to%20solve%20then%20find%20the%20right%20tools%2Fformulas%2Fprocess%20to%20get%20there%3F%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EMy%20logic%20is%20and%20I%20used%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3COL%3E%3CLI%3E%3CSPAN%3ELook%20at%20Account%20Name%20(C2)%20does%20it%20equal%20%E2%80%9Cmonthly%20salary%E2%80%9D%20and%20return%20binary%20for%20Yes%20(1)%20or%20No%20(-)%20I%E2%80%99m%20using%20%3CSTRONG%3E%3DIF(Table1%5B%40%5BAccount%20Name%5D%5D%3D%22monthly%20salary%22%2C%221%22%2C%220%22)%20%3C%2FSTRONG%3Eto%20find%20my%20binary%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EIf%20that%20is%201%2Fyes%20then%20check%20to%20see%20if%20the%20employee%20left.%20This%20is%20where%20I%20fail%20to%20get%20my%20answer.%20I%20attempted%20a%20%3Dif(and())%2C%20I%20attempted%20to%20add%20columns%20that%20may%20assist%20with%20getting%20to%20my%20answer%20(one%20of%20them%20being%20the%20Left%20column%20(column%20F%20in%20the%20Table).%3C%2FSPAN%3E%3C%2FLI%3E%3COL%3E%3CLI%3E%3CSPAN%3EMy%20issue%20comes%20with%20my%20data%20always%20includes%20the%20all%20employees%20regardless%20of%20join%20date%20(even%20with%20Joe%20Blow%20not%20there%20Jan%20he%20still%20shows%20just%20as%200s)%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FOL%3E%3CLI%3E%3CSPAN%3EI%20want%20to%20find%2C%20does%20a%20person%20earn%20a%20monthly%20salary%3F%20If%20yes%20then%20check%20to%20see%20if%20he%20or%20she%20has%20left%20the%20company%20and%20if%20no%20then%20count%20him%20or%20her%20towards%20the%20month%E2%80%99s%20headcount%20and%20only%20count%20the%20employees%20still%20there%20but%20count%20them%20in%20months%20they%20were%20actually%20working%20(answer%20being%202%2C3%2C2)%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FOL%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1778931%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1779061%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Unique%20Headcount%20Counting%20Issue%20(Not%20Able%20To%20Get%20Headcount%20%23%20Right)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1779061%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F830570%22%20target%3D%22_blank%22%3E%40Jpalaci1%3C%2FA%3E%26nbsp%3BPerhaps%20like%20in%20the%20attached%20revised%20file%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent Visitor

My goal is to uniquely find a headcount and movement (employees join or leave) per month and put this in a Pivot Table. My data is formatted like it is in the Table.

 

My issue is when I do a count of Employee number (Empl #) and based on a filter of monthly salary, I don’t get a correct count when the employees join and/or leave and get a constant headcount in the Pivot Table of 3 when it should be 2, 3, 2 for Jan, Feb, Mar.

  1. How to do I fix this?
  2. What is the logic to approach this? How would someone look at this and pick what method to fix this? This is more of a how did you get to your answer sorta question. Do you write down/think how and what to solve then find the right tools/formulas/process to get there?

 

My logic is and I used:

 

  1. Look at Account Name (C2) does it equal “monthly salary” and return binary for Yes (1) or No (-) I’m using =IF(Table1[@[Account Name]]="monthly salary","1","0") to find my binary
  2. If that is 1/yes then check to see if the employee left. This is where I fail to get my answer. I attempted a =if(and()), I attempted to add columns that may assist with getting to my answer (one of them being the Left column (column F in the Table).
    1. My issue comes with my data always includes the all employees regardless of join date (even with Joe Blow not there Jan he still shows just as 0s)
  3. I want to find, does a person earn a monthly salary? If yes then check to see if he or she has left the company and if no then count him or her towards the month’s headcount and only count the employees still there but count them in months they were actually working (answer being 2,3,2)
3 Replies
Highlighted

@Jpalaci1 Perhaps like in the attached revised file? 

Highlighted
That’s it! Thank you. How did you do it?
Highlighted

@jpalaci22 Entered a formula in column G and set the "Left?" filter in the pivot table to "-".