Pivot table for two different data variables


I have the following table


Name type  Cost    overhead
amy     C1   23     12
bob     C2   129    17
cat     C2   36     45
doo     C2   100    32


I owuld like to create a pivot table that looks similar to:
the standard pivot table.  (Please ignore type for now.)

     name Data Total
     amy Sum of overhead 200
       Sum of cost 12
     bob Sum of overhead 130
       Sum of cost 23
     cat Sum of overhead 90
       Sum of cost 19
     doo Sum of overhead 87
       Sum of cost 34
     Total Sum of overhead 507
     Total Sum of cost 88



But with overall total  (overhead + cost) for each 
individual instead of total the two types separately.
Any help would be totally appreciated.
Thanks,
pac





0
chariya (9)
12/17/2004 4:52:38 AM
excel 39879 articles. 2 followers. Follow

2 Replies
1257 Views

Similar Articles

[PageSpeed] 34

Hi

to create a calculated field in your pivot table, select a cell in the pivot 
table and choose from the pivot table icon on the pivot table toolbar - 
formulas - calculated field
give it a name
then after the = sign
double click on the cost field
type a +
the double click on the overhead field
then click on Add then OK
you can now hide the cost & overhead fields if you want

Cheers
JulieD


"comp" <chariya@verizon.not> wrote in message 
news:qCtwd.7700$E_6.5624@trnddc04...
>
>
> I have the following table
>
>
> Name type  Cost    overhead
> amy     C1   23     12
> bob     C2   129    17
> cat     C2   36     45
> doo     C2   100    32
>
>
> I owuld like to create a pivot table that looks similar to:
> the standard pivot table.  (Please ignore type for now.)
>
>     name Data Total
>     amy Sum of overhead 200
>       Sum of cost 12
>     bob Sum of overhead 130
>       Sum of cost 23
>     cat Sum of overhead 90
>       Sum of cost 19
>     doo Sum of overhead 87
>       Sum of cost 34
>     Total Sum of overhead 507
>     Total Sum of cost 88
>
>
>
> But with overall total  (overhead + cost) for each individual instead of 
> total the two types separately.
> Any help would be totally appreciated.
> Thanks,
> pac
>
>
>
>
> 


0
JulieD1 (2295)
12/17/2004 4:59:28 AM
You could add a column to the source data, and calculate the total 
there. Add that field to the pivot table, and remove Cost and Overhead.

comp wrote:
> I have the following table
> 
> 
> Name type  Cost    overhead
> amy     C1   23     12
> bob     C2   129    17
> cat     C2   36     45
> doo     C2   100    32
> 
> 
> I owuld like to create a pivot table that looks similar to:
> the standard pivot table.  (Please ignore type for now.)
> 
>      name Data Total
>      amy Sum of overhead 200
>        Sum of cost 12
>      bob Sum of overhead 130
>        Sum of cost 23
>      cat Sum of overhead 90
>        Sum of cost 19
>      doo Sum of overhead 87
>        Sum of cost 34
>      Total Sum of overhead 507
>      Total Sum of cost 88
> 
> 
> 
> But with overall total  (overhead + cost) for each 
> individual instead of total the two types separately.
> Any help would be totally appreciated.
> Thanks,
> pac
> 
> 
> 
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
12/17/2004 11:54:37 PM
Reply:

Similar Artilces:

Updating a table from Form Entry
I have a form that pops up when a user clicks a button to Print said form. The form is for the user to enter the name of the table and any footnotes for that particular form, this form is connected to a table that stores this information for everytime the report is printed. THe problem I having is that when the user enters the information and clicks [PRINT] the report is blank, when I open the table the info the user has entered has not updated onto the table yet. Is there a way so that when the user clicks [PRINT] the footnote table will update BEFORE the report prints? Larry -- Neve...

Linking Combo box to pivot table?
Hi All, I have a pivot table which has teams on the left, months on the top and a count of the calls in the data area. What I would love is that if I could have a combo box on a worksheet which when I select a team from its list it only shows me that teams data in the pivot table. Is this possible please?? -- Adam ----------- Windows 98 + Office Pro 97 You could move Team to the page area of the pivot table. Select a team, and the pivot table will only show its data. Adam wrote: > Hi All, > > I have a pivot table which has teams on the left, months on the top and a > c...

Copy field data to multiple places
Newbi here.... I have a access 07 file of about 1000 records (rows) and a field (column) I'll call the "project number". All the records do not have the project number inserted as of yet. Is there a simple means to insert a project number in say 50 records at a time, another project number in another 75 records etc. Copy/Paste will do it but may take months to enter. Any suggestions appreciated. TIA On Wed, 27 Feb 2008 15:31:05 -0500, "Meebers" <justme@idontkno.com> wrote: >Newbi here.... I have a access 07 file of about 1000 records (rows) and ...

Pivot Tables & changing data
Hi - I have never used pivot tables in Excel before so hopefully what I am going to ask is possible and not too complicated for me.... :o) I am working with Excel 2003. I have a pivot table already set up and the information is pulling data from a row titled "sum of Subscriber". I added new data in a new column from the main spreadsheet and I would like to pull the data from there. Its titled "Adj Subscriber". Is it possible to switch it? If so, how? Thanks, Anna Marie Anna wrote: > Hi - I have never used pivot tables in Excel before so hopefully what I am &...

how to edit my x-axis data on a line graph
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel my x axis data on my graph is just showing numbers 1 2 3 4 5 6 7 8 9 10 11.... and it is suppose to show the years. I can't figure out how to change these values ...

SQL Back-end / Access Front-end using linked tables????
I have a backend that contains tables that I pull Driver information, Customer information , etc. (these are MAS 200 accounting software tables that are exported nightly to the SQL backend). I also have on the backend tables that I push information to; information that we enter on the forms located on the front end. I might not have this set- up correctly, but I'm linking all tables on the back end to the Access database on the front end. When trying to set up an Auto Lookup off of a query I've created in Access it says I need to go into the table change Data Type to the Lookup wizard,...

SOP History tables
We are trying to determine how some of the fields in the SOP30200 and SOP30300 tables are populated when contracts are invoiced since we are working on a conversion. The fields are: SOP30200 CUSTNMBR -we assume this is coming from the Bill To Customer Number on the Contract Header (SVC00600) PRBTADCD -we assume this is coming from the Bill To Address Code on the Contract Header (SVC00600) PRSTADCD (this is supposed to be a Ship To Address Code but we aren’t sure where it’s coming from since you don’t enter a main address code on the header – we don’t use the Sites button – most of the...

Data from Access query to Excel
To pull data from an Access 2003 database, I have created the queries in Access, then import into Excel. The problem is that all the numbers that are pulled into Excel are text and need to convert them into numbers to run formulas on. I have converted a few sheets by hand, but, some have will over 50,000 rows. Is there a function to select all number colums (the colums are the same through out the sheets) and convert? Thanks There are instructions here for converting text to numbers: http://www.contextures.com/xlDataEntry03.html You can select all the columns, and only the num...

Pivot table fields missing on all computers
I have Excel 2007 installed on two computers. I also have Excel 2010 beta installed on one of the computers. I am unable to create a pivot table on ANY of them. None will show the field list. All copies of Excel work fine in all other aspects. I have searched the net for solutions and found many others have the same problem but few suggestions to fix it. Many questions never got any response at all. I did find a reference to try removing the .xlb file from the XLStart folder. I did, with no results. I have done all the proper show/hide clicks. I even tried using the ...

How to delete duplicate data
Hi, I am using excel to consolidate monthly room booking data. I have a date column and time column. May I know how to to delete those rows which contains duplicate data with same date stated in the date columnand and same time range in the time column? Thank you. Data>filter>advanced filter, unique records only and copy to another location -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "PL" <PL@discussions.microsoft.com> wrote in message news:F45DAE2B-252D-4E17-81D8-0F4C060BCB21@m...

Summary of Difference between dates in years, months, days
I need to calculate the difference between 2 dates and then total them. Here's what I have so far: From To Length of Service 01/09/2003 31/01/2010 6y 4m 30d 01/06/2000 30/11/2002 2y 5m 29d Total of Service: ?????????? I've used the following formula to calculate the total days worked: =DATEDIF(A4,B4,"Y")&"y "&DATEDIF(A4,B4,"ym")&"m "&DATEDIF(A4,B4,"md&...

Compressed Outlined Data Copy
I need to copy only the data that is compressed in Grouped rows and not all the rows when they are expanded. When I copy and paste the compressed data, I get all the data that is within the group, and not just the compressed data. For example: I need just the following (which is compressed): Ship $ Line $5,278.00 CYP Total $133,122.00 TI Total $34,660,135.00 Vic Total $34,798,535.00 Grand Total when I copy it (D2:E17, but only showing 5 lines because of the compression), it pastes 16 lines: Ship $ Line $123.00 CYP $44.00 CYP $4,444.00 CYP $667.00 CYP $5,278.00 CYP Total $123,124.00 TI $...

Using a variable for a chart limit
Since I got instant help on my last query, is there any way to use a cell reference as an axis minimum or maximum in format axis? It seems impossible, but there is a lot of experience out there. Thanks in advance. -- Vince F Hi, See Jon's information http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html Cheers Andy Vince F wrote: > Since I got instant help on my last query, is there any way to use a cell > reference as an axis minimum or maximum in format axis? It seems impossible, > but there is a lot of experience out there. Thanks in advance. -- Andy Pop...

inverting data #2
Please help, I'm stuck! I conducted a survey where respondants each gave answers to 30 questions using a Likert scale i.e. they answered either 1,2,3,4 or 5 to each question. The data is in the following form: Question # q1 q2 q3 q4 q5 1 2 4 2 1 2 2 4 1 2 1 4 3 4 4 2 1 4 4 2 3 2 2 5 5 2 3 4 5 3 My problem is that half of the questions were asked in a negative direction to avoid possible response bias. For the answers to these questions, I need to reverse the answers. If the repondant answered 1, I want to record 5, if 2 then 4, if 3 then 3, if 4 then 2, if 5 then 1. Is there an...

Getting an UnBound control value into a Table field??????
I have a form that calculates a production rate in "parts per hour". This form needs to be able to differentiate between Line work, Cutter work, Side work, and Blister work. I created a drop down list for selecting the "Study Type" as listed above. I then created a text box for each "study type". When I select the "study type" each text box checks to see if the selection applies to it and makes the calculation if it does apply. The code for the "Line" study is as follows: =IIf([StudyType]="Line",2700?/[SecondsPerPart])...

compare two columns with different ranges in two worksheets
I need to compare two columns of data in two different worksheets and display a third one. Here it is an example: -(worksheet1!A1:A10), (worksheet1!B1:B10) and (whorksheet2!C1:C25) -this is my query, if C5 is already in (A1:A10) I want to display B5 in worksheet2!D5 I think it is tricky because you need to identity which row in the A1:A10 is equal to C5 to display B5 and the range are different. you could save my day chris90 In worksheet2!D1: =if(isna(vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)), "", vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)) HTH Kostis Vezerides brilliant, ma...

Retreiving data
We move mailbox on a new server but somethimes we receive the following message: "outlook is retrieving data from the OLD exchange server" Can anyone help me -- Dambo On Thu, 1 Feb 2007 01:21:01 -0800, Damb0 <Damb0@discussions.microsoft.com> wrote: >We move mailbox on a new server but somethimes we receive the following >message: "outlook is retrieving data from the OLD exchange server" > >Can anyone help me What steps did you do to decommission the old server? If you give us an idea of the version of the old and new ones we can post the right help ...

How do I read in a record / row from a table.
Hi, I have a form where I need to read in the parameter setting for the current user. I know I can use DLookup but I have to lookup about 14 different parameters off a single row. But that seems quite inefficient. I need to read the record and extract the values from the record / row. Thanks. -- Dennis Dennis Here's one possible approach ... there are many ... You could create a form that is bound to that record, and make the form hidden. You can refer to controls on the form with: Forms!YourForm!YourControl Regards Jeff Boyce Microsoft ...

Help Required with Macro to Manipulate Data
I have a spreadsheet(s) with the following data on it (this is imported from a text file) A B C D 1 Branch Date Total 2 4501 030204 29 3 4 4501 030204 14 5 6 4502 030204 331 7 8 4502 030204 52 9 10 4503 030204 54 11 12 4503 030204 85 Hi Alay and what do you want to achieve :-) -- Regards Frank Kabel Frankfurt, Germany "Alan T >" <<Alan.T.12jthe@excelforum-nospam.com> schrieb im Newsbeitrag news:Alan.T.12jthe@excelforum-nospam.com... > I have a spreadsheet(s) with the following data on it (this is imported ...

Printing results on different pc and different printer
Hi, regarding printing functions in windows i am a newcomer. Thats why i dont understand some phenomenon : I am working with MFC. My report uses Times New Roman and Arial. Report was developed under PC A with Epson Printer. PC B using Epson produces the same output. PC C with Lexmark produces a report with tiny capitals, that are barely readable. PC A with lexmark Printer produces a readable result, but different to Epson Printer. What did i forget to implement in my program? How can i produce a result, that is independable from the printer used? Thanks in advance, Matt Are escape seque...

Change database source for pivot table
Hi I've built a pivot table based on an Oracle database. however I want to change the source database (from test to live) but can't figure out how to change the source database. Any ideas? Cheers Neil Just answered my own question, in the query go into table definition and change the database "Neil" <neil@funkcity.demon.co.uk> wrote in message news:%23yauyKKpFHA.620@TK2MSFTNGP15.phx.gbl... > Hi > > I've built a pivot table based on an Oracle database. > > however I want to change the source database (from test to live) but can't > figu...

Remove Data Format Change
I have a program that puts a database's data into an excel spreadsheet. The problem is that Excel is constantly changing the numbers to dates, which corrupts the data. I know I can switch the column data type to Text and that will fix it, but the problem is that when the data is written to the spreadsheet it overwrites that column data type and Excel does the formatting that changes my data. How can I turn this off so Excel stops trying to recognize data types and change their format? Please help because this is causing major problems for us. Thanks, ACFalcon >>I have a ...

Data
I can not see the entires I made for the past month. What can I do? In microsoft.public.money, Magnus wrote: >I can not see the entires I made for the past month. What >can I do? I don't know what would cause that. http://support.microsoft.com/default.aspx?scid=kb;en-us;182608 describes using -s switch. That is a simple safe thing to try. http://support.microsoft.com/default.aspx?scid=kb;en-us;274584 describes salv.exe. These may not solve your problem, but they are what I would do. I would also review my backup file situation. ...

External data changes not reflected
I am frustrated by something that is probably simple and due to my lac of knowledge... I had some 10 rows of external data (a named range in anothe spreadsheet) and I was copying it in just fine. Then I extended the source to have 20 rows and changed the length o the named range now when I refresh the data in the destination it onl brings in the first 10 rows. Even If I create a NEW desination workbook file it still doesn't pic up my new BIGGER named range definition(so it isn't something local t the WKS file) I presume it muct be keeping some definition around in cache or in ...

Table for Resource ID
Does anyone know the table name in SQL Server where all the Resource IDs are stored? I am looking for a description on ech of the Resource IDs in Great Plains 7.5. For example RESOURCE ID="899" has a DISP_NAME="Account Analysis Defaults"; what is the Technical Name for this ID? Thanks ...