count of totals for pivot table
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 |
All times are GMT +1. The time now is 01:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com