Pivot Table question

  • Thread starter Thread starter Scott Riddle
  • Start date Start date
S

Scott Riddle

I have a 1 column list of items like this:
Fruits:
Apples
Oranges
Pears
Oranges
Pears
Oranges
Pears
Apples
Apples
Apples

How do I get a pivot table to give me the count of each fruit along
with the number of fruits listed? I change the layout of my pivot
table to have Fruits shown for ROW and Count of Fruits for the DATA.
How do I now get it to show me the count of different fruits? I am
looking for the number 3. It shows me each of the fruits but no count
as to how many different types. Do I need to enter a formula somewhere
or can it be formated somehow into the pivot table? I want to be able
to do this in the pivot table and not a seperate formula in a cell.

Scott
 
Add a column to the source data, which will return a 1 for the first
instance of each item. For example, in cell B2:

=IF(COUNTIF(A$2:A2,A2)=1,1,0)

Copy the formula down to the last row of data.

Expand the pivot table source range to include this column, and add the
new field to the data area of the pivot table, as a SUM.
 
Back
Top