Pivot table functions

[Dale Kolls]

Is it possible to do a distinct count within a pivot table?

 

[Earl Kiosterud]

Dale,

If you mean that you want a count of each grouped item (instead of the usual
SUM), yes. If XL2002, double-click the title for the data item (upper left
of pivot table) and change from SUM to COUNT or COUNTA. If in XL97, go to
step three of the wizard, double-click the item in the data area, and change
similarly.

 

[Guest]

That will give me the number of all the items, but what I
need is the number of unique items.
Make any more sense?

 

[Dave Peterson]

You could use a helper column in your original data:

=if(countif($a$2:a2,a2)>1,0,1)

and drag down.

The first occurence will get a 1 and the subsequent will get 0's.

Then include that in your pivottable.

 

[Gabhan Berry]

Dale,

You could try using a Caclculated Field within your pivot
table. This gives you a new measure (data field) built
using a custom formula that can reference the existing
dimensions (fields) within the pivt table.

Gabhan Berry
AI Prediction and Forecasting in Excel
http://www.pececillo.com

 

[Earl Kiosterud]

Dale,

Oh. Well, why didn't you say so?

Then try this:

=SUM(N(FREQUENCY(A:A,A:A)>0))