Excel 2000: Multiple Column Sums In Pivot Table

[goopit]

Hello All,

I have a report with columns of various general ledger account numbers and the dollar amounts coded to each are filled in below them. The rows contain various customer names. I thought Pivot Table would allow me to sum up the different columns for all the diffent customer names, but only the first column I move into the Pivot Table is summed. The next one is counted and I can't find a way to change that.

Either I'm doing something wrong or perhaps Pivot Table isn't the best tool to use for this report. Any help will be appreciated.

Thank You,

goopit

 

[xlbo]

1st question - I'm not sure how your data is layed out - could you clarify with a small example

Typical layout for a pivottable would be:

GLAcct1 Cust1 Amt
GLAcct1 Cust2 Amt
GLAcct1 Cust3 Amt
GLAcct2 Cust4 Amt
etc etc etc

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[goopit]

It's laid out like this...

G/L# G/L# G/L#
Home Depot $10.34 $1.34 $3.45
Lowe's $12.44 $0.85 $5.66

 

[Adnane]

Hi, maybe you could try to tight click on your pivot, then select Pivot Table Wizard, then Layout. Once there you can double click your data field and check Sum instead of Count

Hope this helps


Adnane

Micorosft North Africa

http://www.microsoft.com/northafrica

 

[bluedragon2]

What are you wanting sums for (By account # or Customer). Also, in Col A (Customers) are there multiple listings of the same customer?



Blue

If I wasn't Blue, I would just be a Dragon...

 

[xlbo]

That's gonna give you problems in a pivot table
How dynamic is the report - ie how often would new customers be added etc ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[goopit]

Thank you, everyone, for your helpful suggestions. Adnane, your recommendation turned out to be the right one for me. Thank you very much.

Sincerely,
goopit

 

[KenWright]

To save going through the wizard, you can simply right click on one of the counts in question, choose 'field settings' and then change count to sum and hit OK. the other thing you can do whilst in this dialog box is to hit the 'Number' button, choose an appropriate number format, and then this will be preserved, even after a refresh.

Regards
Ken....................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[SkipVought]

goopit,

What you have is akin to a PivotTable report. You data is NOT in a good format for data analysis tools like Pivot Table. It really needs to be decomposed into a format like what xlbo posted originally.

Seems to me that you just need to sum the columns and rows to get totals for G/L's and Customers.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KenWright]

I think Skip is right about the format of your data, and so if that is the case, then assuming you still wanted to create a proper Pivot table from it, you would need to convert it into a database table as opposed to a summary table. And it just so happens , that John Walkenbach has a tip on his site that allows you to create a Database table from a summary table, for use as a Pivot table source:-

http://j-walk.com/ss/excel/usertips/tip068.htm

Regards
Ken................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[k2work]

Once you drag a data item into the data area you should click on the column heading.. now the autocalc button should be available (not greyed out). Click on the autocalc pull down and select SUM, Average etc... if you only see "COUNT" available within the list and the others are 'greyed out' then perhaps that data item is originally coded as 'text' .. it can be holding numbers but unless the original table has the data item defined as 'number' you'll not be able to Sum, or average it... just count the entries.

Once you have your total in the data area, you can remove the original data item.

for instance if the data item was a string of numbers
MY COUNT
2
3
4
and now you added the AVERAGE OF MY COUNT= 3
you can now remove the "MY COUNT" field,
leaving just the TOTAL FIELD (AVERAGE OF MY COUNT)

Hope this helps

 

[KenWright]

By the way, the reason it defaults to a count instead of a sum is because you have blanks in the raw data. If you select all your data beforehand, do Edit / Go To / Special / Blanks, type 0 and then hit CTRL+ENTER, this should put a 0 wherever there had been a blank. This should stop it defaulting to Count.

Regards
Ken................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------