Pivot Table 2010 COUNT DISTINCT Salesman

[SalimJ]

Hi,

I have a Excel list for Sales data with 1 record per salesman/period.

I have created a pivot table in compact form from that list where in the Report Filter section, I have the period (year/month). In the Row Labels section, I have Cluster/Dept/Salesman. In the Values section, I want to display #Salesman, Total Sales, etc.

What I want to do is: whatever the number of periods I choose from the Report Filter, the #Salesman must display the correct number of salesmen. Actually, it is displaying the number of salesmen * the number of periods.

Any idea on how to solve this problem.

Many thanks.

Regards,
Salim.

 

[Jerry Sullivan]

Fazza, Thanks for your sharing your thoughts on this.

I haven't used PowerPivot much but it turns out it does have Distinct Count as one of its "Summarize by" options.

I was able to make an on-the-fly Pivot that gave the desired Distinct Counts by Employee quickly and easily.

Salim, You might consider using PowerPivot as an option. It can be downloaded for free for users of xl2010.
It takes some setup to install, so the decision about whether to go that route might depend on whether just you and a few coworkers are using this, or you are needing to distribute the dynamic file to other users who won't have PowerPivot installed.

Cheers!

 

[SalimJ]

Unfortunately, using PowerPivot will be quite complicated as there are about 25 users who will use the report. Also, installing PowerPivot is not straight forward as there are other modules to be installed.

I'll be quite busy this week for Quarterly reports. I will test thoroughly your solution next week and revert back for a feedback.

I'll also investigate on the cost for the company to migrate to XL2013, may that would be a solution if not too expensive.

Anyway, I'll keep you updated.

Thanks again for your help.

Regards.

 

[SalimJ]

Hi am back again.

Jerry, I've implemented your solution with the help of a programmer. It's working fine. Therefore, XL2013 will be for later.

Thank you very much for your help.

Regards,
Salim.

 

[Jerry Sullivan]

Hi Salim, It's nice of you to follow up with your resolution. I'm glad to hear that helped.