Pivot table date as a value

Question

I've got a listing of data which includes:

<br>Class Title	<br>Presenter	<br>Class date
Banner Grant Inquiry	Becky North	01/01/2013
Banner Grant Inquiry	Becky North	01/01/2013
Banner Grant Inquiry	Becky North	01/01/2013
Banner Grant Inquiry	Becky North	01/01/2013
Banner Grant Inquiry	Becky North	01/01/2013
Banner Navigation	Becky North	02/16/2012
Banner Navigation	Becky North	04/20/2012
Banner Navigation	Becky North	07/31/2012
Banner Navigation	Becky North	07/31/2012
Banner Navigation	Becky North	07/31/2012
Banner Navigation	Becky North	09/06/2012

I am attempting to get a pivot table that reflects the number of dates each class title was held.

For instance, I want to see:

Becky North                   5

• Banner Grant Inquiry  1
• Banner Navigation      4

Instead I see:

Becky North                  11

• Banner Grant Inquiry  5
• Banner Navigation       6

I have Class Date in as a Value using Count. How can I group? The data includes each student who attended the class, but I am not using that information in this particular pivot table.

TIA

Dawn Bleuel

Answer 1

Hi,

Using the traditional Pivot Table this cannot be done.  Since you are using Excel 2010, you can do this by downloading installing the free PowerPivot add-in from Microsoft.

Once downloaded and installed, you may write a simple calculated field formula in the Pivot Table as

=DISTINCTCOUNT([Class Date])

If you do not want to install the PowerPivot add-in, then you may use a formula based solution as shown at this link on my website

http://www.ashishmathur.com/count-uniques-with-conditions/

Hope this helps.

Answer 2

You are welcome.

Answer 3

Thank you!

That was very helpful once I figured out how to use Power Pivot. :)

Answer 4

Excel 2010 PivotTable

With regular PivotTable and helper column.

http://58918aa17d4002474361-7ac2b7ee2bc9d74042ff2920135ae995.r25.cf1.rackcdn.com/04_17_13a.xlsx