Pivot Table - Employee Count

[Kelli*]

Hello,

As per the attachment tab Data, I am trying to get a pivot table Column I:K to give me the total of employees per cost center for the selected dates e.g. over a week a full time employee will more than likely appear a minimum of 5 times, plus allowances.

I don't want it to show a count for 1 employee as 12, I want it to count as 1 per employee.

Unique identifier is the Emp # in column C

Note: Date range is from 8/1/2018-14/1/2018

**The true result you will find in the tab Unique Values i.e.

Cost Centre Count of Emp #
40200 2
40201 1
40202 1
40203 1
40210 1
40216 3
40221 3
40222 1
40238 3

I can't figure out how to stop it counting every record more than once.

Trying to work smarter, not harder......
Thank you for your assistance, Kelli

 

[NARAYANK991]

Hi ,

Depending on your Excel version , the approach will vary.

See this link :

https://stackoverflow.com/questions/11876238/simple-pivot-table-to-count-unique-values

Narayan

 

[chirayu]

Note: This does not take into account "Facility" as part of the calculation. Only Emp# & Cost Centre & Date Range

 

[GraH - Guido]

or, alternative to pivot, via Power query group by, if available that is.
All steps via the UI.

 

[rahulshewale1]

hii @Kelli* ,


See if it is ok ,


i used helper column in pivot data.


Regard
Rahul shewale

 

[Kelli*]

Hi ,

Depending on your Excel version , the approach will vary.

See this link :

https://stackoverflow.com/questions/11876238/simple-pivot-table-to-count-unique-values

Narayan

Hi Narayan, I love this and I will keep this for future reference. I have recently seen slicers and dashboards that I would like to get into at some stage.

I'm wondering, instead of using a pivot table to give me the unique count to populate another part of my worksheet, and skip the pivot all together.

Is there a formula that will still give me the unique count for Emp#, Cost Center, and factors in a range of dates?

 

[NARAYANK991]

Hi ,

The attached file gives one such formula in column L. Another formula is available in this link :

https://chandoo.org/forum/threads/data-validation-to-list-unique-values.37869/#post-227330

I would like to point out that the data on the two sheets is not the same. Call centre 40201 has 3 employees in the tab named Data , whereas in the tab named Unique Values , there is only one.

Narayan

 

[Kelli*]

Thankyou that's perfect!

 

[Kelli*]

hii @Kelli* ,


See if it is ok ,


i used helper column in pivot data.


Regard
Rahul shewale

thank you, this helps more than you know

 

[Kelli*]

or, alternative to pivot, via Power query group by, if available that is.
All steps via the UI.

View attachment 51128

Hi Guido, Sorry but I'm not at the power pivot level yet and I love your quote!
Have a great weekend!!

 

[GraH - Guido]

Hi kelli, Pablo was a smart man indeed.
No worries if you do not feel ready to use Power Query yet. Don't let the name Power Query scare you off though. It actually is rather simple to start using. But yes the better you know some legacy features, the easier you will find your way in PQ. But dare to try, I would say. Google some video tutorials by Mike Girvin or Oz du Soleil.

 

[Kelli*]

hii @Kelli* ,


See if it is ok ,


i used helper column in pivot data.

Hi Guido, Sorry but I'm not at the power pivot level yet and I love your quote!
Have a great weekend!!

Hi Rahul, I have tried to use your formula in the test column only, and I'm not getting a count of 1 for each employee that appears between dates 8/1/2018-14/1/2018. What am I doing wrong?

Kelli

Regard
Rahul shewale