Help using Pivot Tables

[Guest]

I am trying to use the pivot table function on data from multiple worksheets
in the same workbook.
I selected "multiple consolidation ranges" when prompted for location of
data to be analysed.
I then chose data from 12 sheets having the same column headings (different
# of rows) and created the pivot table.
The problem is, the pivot table created shows the correct columns but shows
the count of values in these columns for each sheet.
I am trying to get the sum of values in each sheet.

Is this possible?

 

[Debra Dalgleish]

In the pivot table, double-click on the Count of Value button at the top
left.
Under Summarize by, choose Sum, then click OK

 

[Guest]

That helped but it just sums the first column.
Let me explain my table.
I am trying to track my spending habits over 12 months.
I have a worksheet for each month.
The columns are as follows "Item, amount & description"
Where items are "grocery, gas, misc,... etc"
Amount is the amount spent.
Description is a further explanation of "Item"
That is, work expense, personal, family, sports etc.
What I'm trying to do is have the pivot table show me the totals for each
item over the 12 month period but also show me a breakdown on how much of
each item was spent on what. That is, a breakdown of Items by description.
It works fine for data in one worksheet.
But when I try doing the same for "multiple consolidation ranges", the
breakdown of Items by decription does not work.
Is this a limitation of using multiple ranges?

 

[Debra Dalgleish]

If you create a Pivot Table from multiple consolidation ranges, as you
discovered, you won't get the same pivot table layout that you'd get
from a single range. There's more information here:

http://www.contextures.com/xlPivot08.html

Perhaps you can store the data on one worksheet, and create the pivot
table from that.

 

[Guest]

Thanks alot for your help.
I copied all the data to one worksheet and it worked.
I was just being lazy and trying to use multiple ranges as a shortcut.
The only problem with copying all data to one sheet is that you now have two
places to update data.

 

[Debra Dalgleish]

If you've combined all the data on one sheet, why do you need the
separate sheets? Perhaps you could use an Autofilter on the summary
sheet to see individual months when required.