Pivot Table Question

[justin7878]

Hello -- I am trying to make a pivot table that will display the number of days worked in a month. Now there could be several entries for each day but basically I want to have a count of days per month by employee.

I don't know if I am explaining this properly so I will give an example

Employee ID Date
54 01/10/2012
54 01/10/2012
54 02/10/2012
54 02/10/2012
54 02/10/2012
54 30/10/2012

What I essentially want is the for a count of days worked. So the results would be the employee worked 3 days out of the 31.

How do I set this up in excel?

Thank you for any help you can give me.

 

[RMS123]

This should work:
Enter the following formula into C2 (Assuming your columns are in A & B) then fill down. This should give you a unique value based on the 2 columns. Label Column "Days Worked" for example.

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

Select Date (Group by month) Employee ID & Days Worked for pivot table.

Regards.