Pivot Tables - Counting Unique Values XL2007

[Paul H]

Hello All,

I have a pivot table set up and I need to show a count of the number of students there are within a certain course. The problem I have is that the same student is on multiple rows as they attend several training units within the course.

So when I use the count option it is counting the same student over and over. I need to try and count unique values only, i.e. only count each student once.

Any help would be greatly appreciated.

Thanks

Paul

 

[Robert Mika]

Look here:
http://www.mrexcel.com/forum/showthread.php?t=447637&highlight=unique

 

[Paul H]

Sorry for the late reply Robert and thank you. I will have a look at this and hopefully it will work.

Cheers

Paul

 

[Paul H]

Hi I am still struggling with this one unfortunately.

Below is an example of what I am trying to achieve, any help would be greatly appreciated.

Name Code Date
ALEXANDRA OWEN 45 7/04/2010
ALEXANDRA OWEN 45 7/04/2010
ARMSTRONG, KRISTY-LEE 43 7/04/2010
ARMSTRONG, KRISTY-LEE 43 7/04/2010
CAROLYN RUSSELL 72 7/04/2010
CAROLYN RUSSELL 72 7/04/2010
EMILY BROWN 53 7/04/2010
KIRSTY ELFORD 40 7/04/2010
KIRSTY ELFORD 40 7/04/2010
LOFT, ROBYN 78 7/04/2010
ALEXANDRA OWEN 45 7/05/2010
ALEXANDRA OWEN 45 7/05/2010
ARMSTRONG, KRISTY-LEE 43 7/05/2010
ARMSTRONG, KRISTY-LEE 43 7/05/2010
KIRSTY ELFORD 40 7/05/2010
KIRSTY ELFORD 40 7/05/2010
LOFT, ROBYN 78 7/05/2010
LOFT, ROBYN 78 7/05/2010
​

I am looking to count the number of unique students that have a certain date. E.G. for the 7/05/2011 the answer would be 4. I am using the Code column as the count within my Pivot table. However at present I can only get 8 by using the Count option within the Pivot table. I have tried to use the Helper column and use =1/COUNTIF(A:A, A2) and then count this answer but this still isn't working as I am counting the Helper column so it only gives a proportionate value for each student code.

Thanks in advance for any help.

Paul

 

[ns.robert]

I googled Pivot table Unique Value and ended up here, then registered as a user so I can comment. After reading various sites, I hit on a very simple solution - tested only briefly. Anyone see any holes in my solution?

Add a dummy column, call it Unique Value Dummy. Populate it with 1. Add it as data, AVERAGE it rather than COUNT it.

 

[ns.robert]

Here's a better solution - it'll put a correct total on the report.

"from http://excelusergroup.org/forums/p/489/1417.aspx

=IF(COUNTIF($A$1:A1,A1)=1,1,0)

Roger Govier
Microsoft Excel MVP"

If it's already listed on this thread, my apologies. I only scanned the thread.