In your raw data for "Heading 1", do you have two rows with oranges on it? If
yes, then the pivottable is doing its job and counting each row with data.

If you're trying to find out a count of unique items, take a look at Debra
Dalgleish's site:
http://contextures.com/xlPivot07.html#Unique

And if your range keeps growing, you may want to use a dynamic range for the
source. Visit Debra's site one more time:

http://contextures.com/xlNames01.html#Dynamic

SteveM wrote:

I have the below table in a pivot table. When I change the sub total field
to a 'count' I would expect it to change to 2 but it stays on 3.

1st Jan

heading 1 apples
oranges 2
pears
bananas 1

heading 1 sub total 3

I need another cell on another worksheet to look up the heading 1 sub total
cell as a 'count' not a 'total'. The source data keeps changing and new
fruits are added to the list etc.

Thanks very much,
Steve

--

Dave Peterson