Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 1st 10, 08:36 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2009
Posts: 7
Default Create Pivot Table Data with Column "Sum" rather than "count" defa

I have a large set of data (20,000 rows and about 100 coulmns). I wish to
select about 5 columns for a pivot "rows" and about 60 columns to place in
pivot "data". Unfortunately, when I drag these columns into "data", all of
them default to "count" of column. I then have to manually change each one to
"Sum". Is there a simple way to change this default to "sum" without a 2nd
step for each field added?

  #2   Report Post  
Old January 1st 10, 09:05 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default Create Pivot Table Data with Column "Sum" rather than "count" defa

If you have any non-numeric data in a field (empty cells are non-numeric!), then
excel will use Count.

But you could take a look at Debra Dalgleish's pivottable addin.
http://contextures.com/xlPivotAddIn.html
http://contextures.com/xlPivotAddIn02.html

It has lots of nice features--including one you'll really like.

Johnny_99 wrote:

I have a large set of data (20,000 rows and about 100 coulmns). I wish to
select about 5 columns for a pivot "rows" and about 60 columns to place in
pivot "data". Unfortunately, when I drag these columns into "data", all of
them default to "count" of column. I then have to manually change each one to
"Sum". Is there a simple way to change this default to "sum" without a 2nd
step for each field added?


--

Dave Peterson
  #3   Report Post  
Old January 2nd 10, 03:25 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2009
Posts: 7
Default Create Pivot Table Data with Column "Sum" rather than "count"

Thanks Dave. and the add-in is also quite helpful.

John ...

"Dave Peterson" wrote:

If you have any non-numeric data in a field (empty cells are non-numeric!), then
excel will use Count.

But you could take a look at Debra Dalgleish's pivottable addin.
http://contextures.com/xlPivotAddIn.html
http://contextures.com/xlPivotAddIn02.html

It has lots of nice features--including one you'll really like.

Johnny_99 wrote:

I have a large set of data (20,000 rows and about 100 coulmns). I wish to
select about 5 columns for a pivot "rows" and about 60 columns to place in
pivot "data". Unfortunately, when I drag these columns into "data", all of
them default to "count" of column. I then have to manually change each one to
"Sum". Is there a simple way to change this default to "sum" without a 2nd
step for each field added?


--

Dave Peterson
.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot (Data Items "Count" vs "Sum") Ken Excel Discussion (Misc queries) 2 November 11th 09 12:46 PM
Pivot Table Report Filter - "OR" instead of "AND" Multiple Filters tommcbrny Excel Discussion (Misc queries) 1 October 29th 09 03:08 AM
Pivot tables - how do I change default from "count of" to "sum of" Cathy H Excel Worksheet Functions 2 November 19th 08 01:31 AM
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next BCB New Users to Excel 7 May 13th 06 10:02 PM


All times are GMT +1. The time now is 05:07 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017