Need sum in pivot table, not count

I have a spreadsheet which I'm trying to show the sum of each item, but the
pivot table wizard only gives me a count.  It will do a sum for each column
or row, but not each item.  I can do this in a snap if I import the
spreadsheet into access and run a crosstab query, but the folks that are
going to be using this cant use access.  here's a sample of my data:


Item          Amount          Month

A02214         0                  JANUARY
A056480015 0              JANUARY
A13              -348          JANUARY
A16EPG-AD   -2000      JANUARY
A18              -1950      JANUARY
A14RS          -20          FEBRUARY
A18              -3397      FEBRUARY
A18-F          -5000      FEBRUARY
A31EP-AD         0          FEBRUARY

and I'm trying to produce this:

Item                  JANUARY      FEBRUARY

A02214              0                      0
A056480015      0                      0
A13                      -348              0
A16EPG-AD     -2000                  0
A18                  -1950                  0
A14RS              -20                  0
A18                      0                  -3397
A18-F              0                  -5000
A31EP-AD         0                      0

Can anyone help me on this?

Thanks.






-- 
moondaddy@nospam.com


0
moondaddy (4)
6/30/2004 3:11:18 PM
excel 39879 articles. 2 followers. Follow

1 Replies
914 Views

Similar Articles

[PageSpeed] 5

moondaddy <mailto:moondaddy@nospam.com> leverde op 30 jun 2004 een
briljante bijdrage in <news:microsoft.public.excel>. Klik op deze link
om het bericht in zijn oorspronkelijke context te lezen:
<news:#cx4sOrXEHA.556@tk2msftngp13.phx.gbl>. 

> I have a spreadsheet which I'm trying to show the sum of each item,
> but the pivot table wizard only gives me a count.  It will do a sum
> for each column or row, but not each item.

Have you tried a Right Mouse Click on your Count field in the pivot table?
Third item from below in the context menu.

-- 
Amedee Van Gasse
http://www.amedee.be

Dit bericht is geplaatst in een nieuwsgroep. Post je evt antwoord of
vervolgvraag graag in dezelfde thread in de nieuwsgroep a.u.b.
0
6/30/2004 3:10:13 PM
Reply:

Similar Artilces:

Passing Criteria to a Pivot Table from combo boxes
Hi, I have done something similar in the past but not quite the same as this. I have a worksheet which has 8 different criteria I need to pivot on. I have a front sheet with 8 combo boxes on it (for the end user to select criteria) and need to pass these values to a pivot table, which in turn outputs various graphs etc from the data returned by the pivot table. The pivot table and graphs work fine but I don't know how to pass the combobox values to the pivot table. Any advice or pointers would be greatly appreciated. Many thanks In one of my models I use a Lis...

Pivot table sort order
Hello, My pivot table (on a seperate sheet in V2003) seems to sort my data in an entirely random order. It is not in the order that the iems appear in the original data, and it is not in size or alphabetical order, and what's more, I can't change the order, even if I use 'Sort & Top 10' or Advanced 'Field Settings. Why is this? How can I determine the order? - Ideally it would be in the order tht the iems appear, but if that's not possible, I could fiddle it, if I could sort it at all! Thanks M My observation was that tinkering with 'Sort & Top 1...

Showing sum of a colun in an oulook view
Hi everybody we are using outlook task to log tasks assigned by techinician Is there any way to show the sum of duration in a view grouped by technician? thans in advance No, Outlook views can't do math other than counting.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "zrod" <zrod@aims-co.com> wrote in message = news:e3G60766FHA.32...

Counting Table (By Date)
Is there a way to make an table/query in Access that counts by date? For example, if I have multiple projects with different start dates, I'd like to have a running count of the months for the project... e.g. Start Date 02/28/2010 03/1/2010 05/31/2010 Project A B C Date 1/2010 0 0 0 2/2010 1 0 0 3/2010 2 1 0 4/2010 3 2 0 ...

Counting formula help needed!
I'm attempting to do a spreadsheet to show our turnover data. I have sheets titled Terms, Hires, 1st Shift, 2nd Shift and 3rd Shift. My terms and hires sheets have a columns for name, shift and month of term/hire. My shift sheets have the months listed down the left side. I need a formula to count the number of 1st, 2nd and 3rd shift employees termed/hired in each month (i.e. count the number of 1's in the shift column and 1's in the month column, etc.). Once I have the initial formula, I can copy and make changes where needed. I've tried everything I can th...

Adding (Sum of) Hours...
I'm sorry to ask such a seemingly trivial (!) question here.. I *have* Googled and XL Helped but cannot get this to work and I'm on a tight timeframe. I'm trying to add a simple column of hours (hh:mm) worked but simply can't find the right combination of cell formatting to give a straight answer! The hours are actually in non-adjacent cells (a3:c3:e3 etc) but if I can get a column addition working, I'll transfer the formula. eg. 6:15 8:00 6:30 9:00 TOTAL =SUM(A1:A4) I understand it's a formatting issue but I've just spent an hour+ trying ...

Need Talento manual
This is a multi-part message in MIME format. ------=_NextPart_000_0017_01C4760C.C61D2360 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Have a Talento TIPP model 2UD machine that I am going to put in our = store. Where can I find an owners manual etc. for this machine? No luck = on Ebay and can't find a web site that lists this sort of stuff. If this is the wrong board, send me in the right direction. Thanks, Dan --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.73...

Pivot tables, linking to a named range as a source to a pivot table
I have created a main worksheet within the same workbook of many pivot tables, and I want to use this same worksheet as the source of information to these different pivot table sheets off of which I create charts. I want to use a named range because there are over 4,000 rows in this main worksheet. I am not sure if I need a "=" to start the reference or what to do. I thought I should just use nameofworksheet!database if I name the range "database", but when I point and click to the sheet, it is just inserting the name "database" without the name of the...

assigning value to a column based on a table of values
I am correcting an exam and I have automatically generated the score of the students. These I have in columns. Further I have a translation code that showes how a score relates to a grade. this is shown below. Here the interval from min to max is the score that produce the grade. For example if the score is between 2 and 5 the grade is 5. How do I make the translation from score to grade automatically? min max grade 0 2 3 2 5 5 5 6 6 6 7 7 7 8 8 8 11 9 11 18 10 18 23 11 Jacob Just use =VLOOKUP(E1,B2:C9,2) where E1 holds the score to be graded, B2:C9 holds the max and grade columns. ...

count formula between worksheets
I am creating a massive workbook with roughly 90 worksheets. I need a formula to count all the numbers in one cell for all 90 sheets and total it in one cell. Any ideas, I'm fairly new at this. =SUM(Sheet2:Sheet90!A1) HTH. Best wishes Harald "Seti" <Seti@discussions.microsoft.com> skrev i melding news:EB0E9665-0CCA-435D-9385-6B78C375BDC0@microsoft.com... > I am creating a massive workbook with roughly 90 worksheets. I need a > formula to count all the numbers in one cell for all 90 sheets and total it > in one cell. Any ideas, I'm fairly new at this. ...

Need to make sure workbook is open before proceeding
Can you help - Users of my form need to make sure Doc2 is open before running the macro. If Doc2 is not open I need the macro to stop and a message box to pop up stating Doc2 is not open. Application.WindowState = xlMinimized Windows("@Doc1.xls").Activate Range("H2").Select Selection.Copy Windows("@Doc2.xls").Activate Range("E1").Select ActiveSheet.Paste Dim Doc2Wkbk as workbook set doc2wkbk = nothing on error resume next set doc2wkbk = workbooks("doc2.xls") 'no drive and no pat...

We need capability to add Tips to sales for service business
-- Dirk McCuistion ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=679dedc6-a41c-4876-b8c0-7cc0e53bf6ae&dg=microsoft.public.pos Dirk, I have the same problem, we are...

need inv/rec/issue template
I need an Excel template that can manage reciepts and issue and track material through multiple step projects. I know that it will be input intensive but we are small making onesies and twosies with multiple inside and outside processes. Thanks for your assistance Mark This sounds like a lot more than just a template, Mark. Sounds like a complete custom application to me. The best I can do is an invoice and sales tracking method in my downloads folder at www.officearticles.com/downloads/ ************ Anne Troy www.OfficeArticles.com "Material Maven" <Material Maven@discu...

Table format
Hi When I run the code below I get "External table is not in the expected format" error at line rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText Option Explicit Public Sub SelectFromAccess() Dim rsData As ADODB.Recordset Dim sPath As String Dim sConnect As String Dim sSQL As String 'Clear the destination worksheet Sheets(1).UsedRange.Clear 'Get the database path (same as this workbook) sPath = ThisWorkbook.Path If Right$(sPath, 1) <> "\" Then sPath = sPath & "\" 'Create the connection s...

Dynamically changing several pivot tables at once
I have several pivot tables within one spreadsheet. All pivot tables have the exact same criteria within the drop downs (eg. country, region, city). The "data" presented in each pivot table is different (eg. revenue, gross profit, net income). Is it possible to link all pivot tables so when a user changes, for example, the country in one pivot table, it updates all other pivot table data with the selected country values? I appreciate any help you may have. Regards, Jason Check this website and run the "- Synchronize 2 Pivot Tables with a Combo Box" tutorial: h...

Need help Money Deluxe and Business 2003
I am re-installing money deluxe and business 2003 and my cd is pretty scratched up. The.cab files have a cyclic redundancy error. Does anyone know where I can download the cab files. I have everything else to install. You won't be able to download them (although I've not tried the files from the trial versions in this instance), but you can get a replacement by contacting MS. See http://support.microsoft.com/default.aspx?kbid=326246 -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send...

"client last name" & client table "last name" same info-join?how
Access job table has "client last name" & client table has "last name". same data different name. when i try to join relationship nothing happens with the data. it is not sharing data for last name.??? Are you sure you want to do that? How many "Smith" or "Jones" or "Fernandes" or "Ivanov" entries might there be in a job or a client table? Joining on "last name" is not a good idea... -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may have received products and services menti...

Pivot Table
Hi all, I have one issue when i get the data through the pivot table option. after creating a pivot table, i enabled the sub-total and the grand total in the pivot table properties. after enabling that the sub-total and the grand total columns appear as #VALUE!. I have office 2003 in OS 2000 proffesion. Please help me Regards Yazh I'd check for errors in the original data for that field. yazh wrote: > > Hi all, > I have one issue when i get the data through the pivot table > option. after creating a pivot table, i enabled the sub-total and the > grand total in...

Pivot Table Toolbar #3
When I right click in the tool bar area and try to include the pivot table tool bar no additional buttons appear but just a blank row in the tool bar area? I'm confused. Do I have a setting wrong in my Excel setup? I was hoping for some options that included the option to include formulas in my pivottable. -- thanks, mc Please disregard question - found my problem - I just needed to reset my buttons. -- thanks, mc "dallin" wrote: > When I right click in the tool bar area and try to include the pivot table > tool bar no additional buttons appear but just a bla...

retieving data from a sql table
Hi, I'd like to be able to automatically fill certain columns in excel with data from an SQL table. For example, I have a column called "item code" and I'd like the columns "item description" and "item cost" to be automatically retrieved from an SQL table when the user inputs the item code. I'm having trouble in two areas. One, getting it to perform automatically once the data is inputed, and filtering the records to include only the description and cost for that specific item code. I've tried using macros and the "Get External Data" in ...

Creating and updating a Table of Contents in MS Publisher 2003
Last year my department started using MS Publisher to create and update training materials for three call centers. One big drawback (the only one I've found so far!) is that unlike MS Word I have been unable to create an automatic Table of Contents, therefore, we've been creating them manually then updating them each time we need to update the materials (which is frequently). Isn't there some easier way to do this? -- Sharon As you have found out, Publisher does not support TOC. Word is more suitable for this. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http...

Count Cell Range
I am having problems figuring out a Count command within a certain range. This is what I am looking for If Cells D1:D20 are less than Cell B2, Count the total cells that are less than B2 I know this is probably easy, but i just font get it Thanks again -- discgolfer_1999 ------------------------------------------------------------------------ discgolfer_1999's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27821 View this thread: http://www.excelforum.com/showthread.php?threadid=473667 Hi! Try this: =COUNTIF(D1:D20,"<"&B2) Biff "...

Pivot Table w/ Access data
I have a pivot table linked to an access database query. I added data in the access query and refreshed the pivot table, but the new data will not populate in the pivot table. The odd thing is that while the pivot table is running I can see the new data populate, but before the pivot table ends populating the new data disappears. I recreated the pivot table to see if the data is actually available to run a pivot table, and the new pivot table works fine. Any suggestions on how to get my old pivot tables to populate with the new data? Hi, This is not completely clear. what version...

Arrange columns (lo
I am using the Pivot Chart Report and the columns are displayed in alphabetical category order. I desire an arrangement of the categories in value order (lo - hi) as excel displays the columns in the regular bar charts. How do I change the sequence of the columns on the Pivot Chart Report?? Would greatly appreciate any help. Tomc Hi, The pivot chart order is based on the pivot table order of the field being considered. Suppose the the data looks like this in the pivot table Values Field Row Label Total Sold A 30 B 12 C ...

Need help updating 1 cell from other cells in same column
Hi and thanks in advanced for your attention. I am trying to do the following: Have cell B12 be updated everyday automatically from 1 different cell when data is found on those cells. Those cells contain a formula that sums 3 cells in one same row. The total is of course put on those cells (B16 - B35). To specify more on this problem let me give in that: B16 represents the money earned on February 1st. B17 represents the money earned on February 2nd B18 the same for Feb. 3rd. and so on. That data is only entered once per day. I tried link B12 to the value on those cells, but ...