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?

0 |

1/1/2010 8:36:01 PM

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

0 |

1/1/2010 9:05:00 PM

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 > . >

0 |

1/2/2010 3:25:01 PM

To sum the 3 highest numbers, following functions can be used in a really nice combination: =SUM(LARGE(A1:A100, {1,2,3} ) ) But how to add an condition to it? Example: Column A below are the scores for induvudual persons in a contest. Column B below is the country they are competing for .. How to add the sum of the 3 best scores for each country ? The correct answer should be 213+199+187=599 for USA and 198+158+135=491 for Canada. A B 141 USA 199 USA 135 CAN 187 USA 158 CAN 145 USA 213 USA 198 CAN 120 USA 180 CAN Reall...

Within a macro I am trying to add 2 formulas to the bottom row of my worksheet. The code is below and has allowed me to add static data. Now I need to do a count or countif (or something else)for column c that will give me the total number of rows that are "01". And for the final column i want to do a sum. Can anyone help? 01 150080380549 0000718649 000000095556 01 150080380549 0000718650 000000012000 01 150080380549 0000718651 000000002080 01 150080380549 0000718652 000000002912 02 150080380549 ???? ??? ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Off...

I have a report based on a crosstab query that counts the number of enquiries from an advertising source for a year and sorts them into months So columns are by date and rows give the source and the value is the count of enquries in the source. I want to total the number of enquiries in the month on the bottom of the report. I have tried putting a text box in the report footer that uses the value field and set it's running sum property to over all but it just gives the sum for the source and not all the sources added together. Is it possible to do this? Peter One of the f...

hello, I am a very basic excel user and I need help to create a formula that will count multiple criteria. For example: I have a worksheet with my data and another to summarize my data. Each row in my data sheet, "Corproate Projects List",tracks the detail of a specific project assigned to my department. Column A tracks whether each individual project is currently open, closed or ongoing. Column D tracks the department requesting the project. The rest of the sheet doesn't matter with respect to my question on how to write the formula...... In my summary sheet I want to count th...

Is there any way to return the data itself into the pivot table data? I used to be able to return the value, not the sum, count or calculation. Now there is not a selection for it in the drop-down box. ...

Hi, Scenario, twelve columns Jan to Dec, rows are for items I have purchased. I now buy something and need to add a row to name the item. If I have used the sum tool and I have the formula for my November total cell L15 as =SUM(L2:L14), if I then need to add a row into my sheet, by clicking in row L15 and going insert row, row 14 is now for adding my purchase, and row 15 is now the totals row, the total cell should now read =SUM(L2:L15) but still reads =SUM(L2:L14), so I have to manually alter the formula, then edit copy the cell and edit paste it as paste special formula across...

I have a spreadsheet of estimate date that is incomplete (some cells have data, some do not yet). I need to be able to count the number of estimates (col A in the simple example below) that are for completed phases (col C)...in other words, the date in col C is in the past. I'll also need to be able sum col A for all completed phases (but this can be done in another cell). In the example below, I would expect the estimate count to be 2 and the estimate sum to be 4500. A B C 1 Estimate Actual ...

When I highlight a block of cells the total would show at the bottom right of the screen. Now, when I highlight the cells, I get 'Count = x" instead of $sum. Why and how to fix. Thanks! Hi PTFisher Right click on "'Count = x" and change it -- Regards Ron de Bruin http://www.rondebruin.nl "PTFisher" <PTFisher@discussions.microsoft.com> wrote in message news:0EC51005-6359-4F6D-96E2-0AAEEF469551@microsoft.com... > When I highlight a block of cells the total would show at the bottom right of > the screen. Now, when I highlight the cells, I get ...

Is there any way to exclude the words Sum of in the data field, and how do you force a pivot table to default to the sum in the data field instead of saying count? Thanks, Xrull 1) You can manually change the data field headings, by typing over the exising headings. You can't use a heading that's the same a a field name in the source data, so you can't change 'Sum of Sales' to 'Sales'. However, you can add a space character at the end of the name, to make it different: 'Sales ' 2) You can't change the default settings for the data fields. If a fi...

just changed to office 2007 and can't select a range of cells to immediately show the sum total on the footer stats bar Have you tried right mouse clicking on the status bar and then choosing the function(s) you want to see? 2007 allows multiple functions (e.g. Sum, Avg, Min). "RobEgg" wrote: > just changed to office 2007 and can't select a range of cells to immediately > show the sum total on the footer stats bar ...

Hello, My question,.... I have a query which count the number of records in a table (as a result of the menuoption view-> totals ->count) as a result of the group by function for unique records. The result is: column1 column2 A 10 B 15 C 8 Now I want to insert a column3 which gives the sum of that counted values. I'd tried something like: expr1:Sum(Count[column2]) The result should be for each record in column3: 33 (result of the sum 10+15+8) It doesn't work. Somebody knows a solution ? Thanks regards, Johan You can always writ...

Hi all, I am fairly new to excel and experimenting some functions with my project. I have a SOLD datasheet with Sold Date (column W) in MM/DD/YYYY format). I am trying to count the items with a few criteria using =SUM(COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2,...criteria_rangeN, criteriaN)) Current formulas are working without any problem. Now, i want to narrow down by months and found out that MONTH functions does not work with current formulas. So i tried adding IF function in front of the current formula Jan=1, Feb=2, ... , Dec = 12 =IF(MONTH(SOLD!W:W)=1, SUM(CO...

Hi, Its a minor point but one that is bugging me. My excel 2000 has stopped displaying the sum of cells which I select with my mouse and has instead started displaying the count - does anyone know how to change this back? So for example, A1 has "10", A2 has "20" and A3 has "30". Previously, if I had clicked on cell A1 and dragged the selection over to A2 and A3, the window bottom right would have shown "Sum = 60". Now, however, it says "Count Numbers = 3". Thanks, Will Hi Will Try right clicking where you see Count, and change the Op...

I have 2 calculation to find out, one that finds all cells in K:K that have a time equal to or less than 4:00:00 (4 o clock) and the other that finds the ones that are equal to or over 4:00:01. Obviously that is easy enough but I only want to include the ones that have a 1 in the corresponding G:G column. I have has a search around on here and gone through several sums using countif, sumproduct, count(if but none are giving out the correct figures. Can anyone assist? Thank you in advance Use SUMPRODUCT. But SUMPRODUCT does not accept whole columns frerences like G:G. If you want tu su...

Hi Folks - I am using DCOUNT like this: =DCount("disp1","qrycombinedispositions","disp=qrycombinedispositions.disp1") I am counting the number of different dispositions based on a control in the report. It works fine. I'd like to sum the individual counts into a grand total. I realize you cannot sum a calculated control. I've tried the workaround of using another text box control that sets the control source to the calculated control name and uses the running sum property. No luck. The name of the dcount calculated control is txtCountDisposition. T...

When I create a pivottable from some imported data Excel (2003 SP2) sometimes chooses to summarize the data by using the function "count" instead of "sum", which I then have change manually. What is it in the database that is triggering Excel to choose either function? If a field contains blanks, or text, the Count function is used. Otherwise, the Sum function is used. Marcus wrote: > When I create a pivottable from some imported data Excel (2003 SP2) sometimes > chooses to summarize the data by using the function "count" instead of "sum", ...

Hi, I have a query that shows the following information It looks at a specific table (table 1) then groups by sales agent, then groups the tariffs that have been sold by that sales agent and then the query has a count on tariffs. I have then built a report to display the results which would look like this :- Dave Brown Standard Tariff 1 EDP Tariff 2 What I want is a nother function in the query that will then sum the count coloumn so the example above the sum coloumn would be 3. i then need the report to sort by the sum. Thanks Glenn wrote: >I hav...

I am trying to cap a cell that I use for discounts. eg the cell could read 35%, but I only want a max of 30% on some products I am using a cell to input a discount into quotations, this cell is then used to calculate sums further below eg g24 = 40% and I use =(E9*F9)*(1-$G$24) later to get my total BUT I want to be able to cap some lines with a lower discount eg some products can have up to 40% ,but others can only go up to 30%. =MIN(SUM(A1:A10),100) This sum works if the cell is formatted as a general number, but not if it is formatted as a percentage. Help please... If row 9 rep...

I have several very large spreadsheets of data for a family history project. I need Excel to be able to tell me how many females (or males or unknowns) were born or died or married in a particular district and/or county/state or country between particular time periods. A rough list would look like this:- Year Qtr Event Surname Given Names Sex Age District County Country 1837 3 B EDWARDS John M Maidstone KEN ENG 1837 3 D EDWARDS Mary F U Hollingbourne KEN ENG 1837 3 M EDWARDS William M U Tenterd...

First, thanks in advance to anyone who can help me with this (probably simple and I just don't get it). I am trying create a simple list of items such as car parts and the result be a cumulative total by day for each part. The trick is I'd like to go through a bunch of hand written tickets and keep typing the number into the cell for mufflers for instance. So in Column A, I have mufflers, air filters, water pumps, etc. Then for today, Feb 4th, I'll have a bunch of hand written tickets and I'd like to go through each ticket and add the number of mufflers in a single...

Pivot table experts, I can't seem to make this work. First field is dragged to "row fields" and correctly becomes my first column. Second and subsequent fields are the same kind of field and are supposed to be the data for each row. So I drag the second field to "data items" and get a column but the data is a Count. So I click on Data and choose Sum. The data turns to Sums. Great. Then I drag the third field to "data items" and it stacks the second and third fields. So I dragged Data to "column fields" and now correctly have second and t...

I have a formula where I need to count instead of sum. Is there a way to do this? Joe =SUMPRODUCT((YEAR('2003-2004'!$A$4:$A$10000)=(Summary!$B$4))*(MONTH('2003-20 04'!$A$4:$A$10000)=(Summary!$D$3))*(('2003-2004'!$I$4:$I$10000)=E4)*'2003-20 04'!$E$4:$E$10000) I tried: {=SUM((MONTH('2003-2004'!$A$4:$A$10000)=(Summary!$D$3)*(YEAR('2003-2004'!$A$ 4:$A$10000)=(Summary!$B$4)*(('2003-2004'!$I$4:$I$10000)=(Summary!E4)))))} Hi Just take out the bit that is the summing part. If it's '2003-2004'!$E$4:$E$10000, just drop it off...

Item # PO # # of Action Date QTY remaining Boxes X4A341SB 9962 1 Out 4/16/2003 43 X4A341SB 9964 2 Out 4/16/2003 42 X4A341SB 9965 55 in 4/16/2003 41 X4A341SB 9967 6 Out 4/16/2003 40 89121 10001 1 Out 4/25/2003 19 BBK57911 10001 1 Out 4/25/2003 -42 CBK38161 10001 1 Out 4/25/2003 23 BBK33904 10001 2 Out 4/25/2003 -1 CBK48181 10001 6 in 4/25/2003 6 ABK03915 10001 8 in 4/25/2003 6 CBK03100 10008 1 Out 4/29/2003 49 CBK30000 10008 33 Out 4/29/2003 49 AB3L1011 10013 8 Out 4/29/2003 129 AB3L1011 10014 7 Out 4/29/2003 128 B48266 10015 4 Out 4/29/2003 32 B...

I am trying to create a supplier delivery performance spreadsheet and I need to be able to show a count of the total number of unique orders placed for each supplier. The worksheet is sub-totalled on a count of the number of receipts made but one order can have one to many receipts. SuppNo OrderNo ReceiptNo 123 9961 1 456 9978 16 456 9982 33 789 9999 46 789 9999 57 Therefore SuppNo 123 has 1 Order(s) and 1 Receipt(s) 456 ...

Hello All, Happy new year ! Maybe someone can help me! I want to know if anyone knows how to activate th autosum/average/count function in excel which is displayed in the righ bottom corner of the screen if you selct more then one cell with specific value. I had it on my screen before but it disapeared somehow and I am bo able to get it back. Does anyone also know from which version this i abvailable? Thanks for your help !! BR// J -- Eagle6 ----------------------------------------------------------------------- Eagle68's Profile: http://www.excelforum.com/member.php?action=getinfo&...