How Do You Group Dates when Using Distinct Count in a Pivot Table?

[ozziewendy]

I have been waiting for my organisation to upgrade to Excel 2013 so I could use Distinct Count in a pivot table I created some time back. I was shattered to find that I lost the ability to group dates.

In the attached file, I wish to have a pivot table that shows me the number of meetings held each month/quarter, and the number of distinct mentors and mentees involved in these meetings. The volume of data is small (the file is the actual data with the names changed) so I have in fact been doing it manually up until now, but really don't want to have to keep doing this. Besides, sometimes we don't get told about the meetings for quite some time so I have to remember to go back and change my table when previous month's data is added.

I am a half a day a week volunteer at a not-for-profit, so can only spend a small amount of time on this. Can anyone explain how I can use the new pivot table count distinct but also group the dates??

Thanks.

 

[Nebu]

Hi:

Hope this is what you are looking for.

Thanks

 

[ozziewendy]

Hi:

Hope this is what you are looking for.

Thanks

Nebu,

Not quite.
I want 2 count columns - one for the distinct number of mentors and one for the distinct number of mentees. your solution isn't showing the right count even for the combination - there are 3 distinct combinations in January, not 4. I don't really follow the countif in the sheet, sorry, how is it meant to work?

Wendy

 

[Nebu]

Hi:

Please find the attached.
Thanks

 

[ozziewendy]

How did you get the months to show as Feb etc, the cell format used should show just as a standard date, I can't figure out how you put the format on. Sorry for the delay in replying, I am only here half a day a week.

Wendy

 

[ozziewendy]

Alright, inspired by your solution I'm now doing the attached. Thanks for your help.

Wendy

 

[Nebu]

Hi:

I guess we both are getting the same results. I used the grouping feature of pivot to get the months please follow the link to learn more about this.

https://support.office.com/en-in/ar...e-report-c9d1ddd0-6580-47d1-82bc-c84a5a340725

Thanks