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

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

Yes the pivot table is doing it's job and counting each row with data but I
want to get a count of all the counts rather than a total (adding up) of the
counts?

Does that make sense? Cheers.

"Dave Peterson" wrote:

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

It makes sense, kind of...

But pivottables count the stuff in the raw data table.

SteveM wrote:

Yes the pivot table is doing it's job and counting each row with data but I
want to get a count of all the counts rather than a total (adding up) of the
counts?

Does that make sense? Cheers.

"Dave Peterson" wrote:

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


--

Dave Peterson