pivot table and multiple "sum of"'s

I didn't use pivot tables much before, so this may seem a silly
question to you. I want to use twice "Sum Of ..." next to eachother.
I'm using the manual configuration method. So maybe what I'm trying to
do is rather difficult this way. If it's easier another way, I'd like
to know.

I'll make clearer what I want using a little example

Sum of French     Sum of English
1                      5 
2                      6
2                      6 
3                      7
4                      8
5                      8

I'm using the pivot table thing in a Windows NT4.0 environment with
Office 2000.

Thanks in advance for your help.
0
2/22/2004 7:06:06 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
509 Views

Similar Articles

[PageSpeed] 46

Hi
how is your source data organized. Lets assume its organized like the
following:
Topic        Language    Amount
top1           French        10
top1           English        25
top1           English        5
top2           English        5
top3            French        7
....

you can use set up the pivot table as follows:
Pivot Row: Topic column
Pivot Data:  SumOf Amount column
Pivot Column:   Language column

The output would look like
            French        English
top1     10                30
top2      0                  5
top3      7                  0




--
Regards
Frank Kabel
Frankfurt, Germany

Stevie wrote:
> I didn't use pivot tables much before, so this may seem a silly
> question to you. I want to use twice "Sum Of ..." next to eachother.
> I'm using the manual configuration method. So maybe what I'm trying
to
> do is rather difficult this way. If it's easier another way, I'd like
> to know.
>
> I'll make clearer what I want using a little example
>
> Sum of French     Sum of English
> 1                      5
> 2                      6
> 2                      6
> 3                      7
> 4                      8
> 5                      8
>
> I'm using the pivot table thing in a Windows NT4.0 environment with
> Office 2000.
>
> Thanks in advance for your help.

0
frank.kabel (11126)
2/22/2004 7:18:23 PM
I'm sorry. I should have been more clear. 
In fact I've got two columns of data, like the amount of units you buy
on one hand, and the price to pay on the other hand. The examples I
give are in fact just mere examples.

I want to get two pivot field next to eachother
one "Sum of x", meaning column 1, the other one "Sum of y" meaning
column 2.
I'm not sure it's possible to get those two "sum of's" above a column
in the pivot table, or left of two different rows.

May I ask you to give a rather extensive explanation. 
I'm just a newbie regarding pivots. 
Any help would be greatly appreciated.

"Frank Kabel" <frank.kabel@freenet.de> wrote in message news:<e1QOliX#DHA.3828@TK2MSFTNGP10.phx.gbl>...
> Hi
> how is your source data organized. Lets assume its organized like the
> following:
> Topic        Language    Amount
> top1           French        10
> top1           English        25
> top1           English        5
> top2           English        5
> top3            French        7
> ...
> 
> you can use set up the pivot table as follows:
> Pivot Row: Topic column
> Pivot Data:  SumOf Amount column
> Pivot Column:   Language column
> 
> The output would look like
>             French        English
> top1     10                30
> top2      0                  5
> top3      7                  0
> 
> 
> 
> 
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
> 
> Stevie wrote:
> > I didn't use pivot tables much before, so this may seem a silly
> > question to you. I want to use twice "Sum Of ..." next to eachother.
> > I'm using the manual configuration method. So maybe what I'm trying
>  to
> > do is rather difficult this way. If it's easier another way, I'd like
> > to know.
> >
> > I'll make clearer what I want using a little example
> >
> > Sum of French     Sum of English
> > 1                      5
> > 2                      6
> > 2                      6
> > 3                      7
> > 4                      8
> > 5                      8
> >
> > I'm using the pivot table thing in a Windows NT4.0 environment with
> > Office 2000.
> >
> > Thanks in advance for your help.
0
2/25/2004 6:44:05 PM
Hi Stevie
take a look at the following tutorials :-9
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.cpearson.com/excel/pivots.htm


--
Regards
Frank Kabel
Frankfurt, Germany

Stevie wrote:
> I'm sorry. I should have been more clear.
> In fact I've got two columns of data, like the amount of units you
buy
> on one hand, and the price to pay on the other hand. The examples I
> give are in fact just mere examples.
>
> I want to get two pivot field next to eachother
> one "Sum of x", meaning column 1, the other one "Sum of y" meaning
> column 2.
> I'm not sure it's possible to get those two "sum of's" above a column
> in the pivot table, or left of two different rows.
>
> May I ask you to give a rather extensive explanation.
> I'm just a newbie regarding pivots.
> Any help would be greatly appreciated.
>
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:<e1QOliX#DHA.3828@TK2MSFTNGP10.phx.gbl>...
>> Hi
>> how is your source data organized. Lets assume its organized like
the
>> following:
>> Topic        Language    Amount
>> top1           French        10
>> top1           English        25
>> top1           English        5
>> top2           English        5
>> top3            French        7
>> ...
>>
>> you can use set up the pivot table as follows:
>> Pivot Row: Topic column
>> Pivot Data:  SumOf Amount column
>> Pivot Column:   Language column
>>
>> The output would look like
>>             French        English
>> top1     10                30
>> top2      0                  5
>> top3      7                  0
>>
>>
>>
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>> Stevie wrote:
>>> I didn't use pivot tables much before, so this may seem a silly
>>> question to you. I want to use twice "Sum Of ..." next to
eachother.
>>> I'm using the manual configuration method. So maybe what I'm trying
>>  to
>>> do is rather difficult this way. If it's easier another way, I'd
>>> like to know.
>>>
>>> I'll make clearer what I want using a little example
>>>
>>> Sum of French     Sum of English
>>> 1                      5
>>> 2                      6
>>> 2                      6
>>> 3                      7
>>> 4                      8
>>> 5                      8
>>>
>>> I'm using the pivot table thing in a Windows NT4.0 environment with
>>> Office 2000.
>>>
>>> Thanks in advance for your help.

0
frank.kabel (11126)
2/25/2004 6:47:36 PM
Reply:

Similar Artilces:

Calculating Sum of limited data set
I have two tables: Measure and Score. The measure table is the parent table containing details for various performance tracking. The Score table contains the monthly scores for the respective measure. I've uploaded some screenshots to help with my inquiry: http://www.flickr.com/photos/9731133@N07/sets/72157600672829561/ I need two separate controls for a Dsum of the numerator and denominator for each individual measure which I can then ultimately use to calculate a performance rate. My current Dsum calculation (which I know is wrong) uses the entire Score table as the domain which pe...

retrieving a sum of matched values in vlookup
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C3F703.3739C000 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Can someone tell what I need to solve this problem? On sheet1 I have a list of records that have many rows for each subject. = Eg. A B C Name ACCT AMT =20 Bill 1101 $ 12.00 =20 Bill 1102 $ 45.00 =20 Bill 1103 $ 22.00 =20 Jeff 1101 $ 10.00 =20 Jeff 1102 $ 80.00 =20 Jeff 1103 $ 17.00 =20 On she...

Sum of sub groups
Hi, I have data as described below: AA1 BB1 CC1 10 EE1 AA2 BB2 CC2 8 EE2 AA3 BB3 CC3 4 EE3 AA4 BB4 CC4 5 EE4 AA5 BB5 CC5 10 EE5 AA6 BB6 CC6 20 EE6 AA7 BB7 CC7 5 EE3 So, basically I have subgroups like above in an spreadsheet. The number of rows in each subgroup are not fixed. It may have any number of rows. The only way I know is the subgroup is ended is when I encounter a blank row. I need to insert a new row at the end of every subgroup and then sum up column D in this newly inserted row. So, for the group 1 I should have a sum of 27 (10+8+4+5), and for second sub group ...

Sum of each grouped item in a report
I have a form that logs time spent on auditing activities for the Internal Auditors in my company. Each time the auditor performs an audit related task, they enter a new record under their name. This means they could have many occurences of the same function. I then created a query/report that breaks down the different auditing activities for each internal auditor. Management has now asked for a report that gives a total for each activity for each internal auditor (example - John Smith: Audit Prep - 2.25, Follow-up - 3.75, Audit - 7.50). I want to figure out how to do this on a rep...

How to make a SumIf range not a range ... but a sum of specific cells
=SUMIF(D3,AL3,"=H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3") Here is what I am trying to do: If D3 = AL3 then I want it to return the sum of H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3 I know it is something simple I am missing ....... Just use If() =if(d3=al3,H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3,0) JPD wrote: > =SUMIF(D3,AL3,"=H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3") > > Here is what I am trying to do: If D3 = AL3 then I want it to return > the sum of H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3 > > I know it is something simple I am missing .........

Sum of Hours Per Month
I have been given a data set which contains work hours and dates in the form of mm/dd/yy. I am trying to sum all the hours worked in each month. A co-worker suggested I use sumproduct but I'm still not able to achieve the process. Additionally, the data crosses over years, so I am not quite sure how to create a fomula that takes the year into account. Any help would be great. Thanks much. -- Zachary Baker Using a pivot table grouped by months and years would be the most straightforward. "Zachary Baker" <Zachary Baker@discussions.microsoft.com> wrote in message ...

sum of blank and non blank
Hi, How do I count blank cells in a range which contains text and separately how do I count cells that are not blank? I used =countblank(range) which works but how do I do the opposite, count cells WITH text in range? Tx, S =COUNTA(range) will count all cells that are not empty in "range". =COUNTIF(range,"*") will count only cells that contain text. HTH Jason Atlanta, GA >-----Original Message----- >Hi, How do I count blank cells in a range which contains text and separately >how do I count cells that are not blank? >I used =countblank(range) which work...

Sum of 3 maximum numbers
I have 12 cells with some numbers. How can I calculate the sum of the three maximum numbers among them? Something like: =SUM(LARGE(A1:A12,{1,2,3})) Ruda wrote: > > I have 12 cells with some numbers. How can I calculate the sum of the three > maximum numbers among them? -- Dave Peterson Just for fun =IF(COUNT(A1:A12)=0,"",SUM(LARGE(A1:A12,ROW(INDIRECT("1:"&MIN(3,COUNT(A1:A12 ))))))) as an array formula, so commit with Ctrl-Shift-Enter. This caters for less than 3 numbers, without an error -- HTH RP (remove nothere from the email address if m...

Calcutate SUM of column in first blank row
Hi All, Hope someone can help me with this. I am trying to find a method of finding the first blank row on a worksheet and perform a sum calculation for column B: example: Before A B ww ww x 1 x 2 x 5 x 6 After ww ww A B x 1 x 2 x 5 x 6 14 I have code to find the first blank row: Dim NextRow As Long NextRow = Range("B65536").End(xlUp).Row + 1 Cells(NextRow, 2) = "test" But of course, I don't want it to say test, but insteade calulate the sum of all values in the column from B2 down (...

SUM of COUNT?
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...

Sum of 6 moving cells
Good day to all, Sheet1: Column A is the date (from Jan 1, 2005 to the current date), Col B contains the hours worked on that date and Col C is hours worked for the last 12 days. I manually type in the hours worked everyday on Col B and there is a formula on Col C to add the last 12 days. There's about 45 units that I keep track of the hours worked. How can I show on a summary sheet, the hours worked of each unit as of yesterday? My problem is that the cell that totals the last 12 days moves down one cell everyday. -- ledzepe --------------------------------------------------------...

Preserve number as text in Pivot table sum of values field
Does anyone know how I can create a table like this: first column: second column: third column: (product name) (product number) (sum of products) I know how to create the first column (via "row labels") and third column (via "sum values"). But for the second column, if I put the fields under "row labels" the names get indented under the product name. If I put the fields under "sum values" the names gets counted. I'd like to preserve the product numbers as text. Thanks in advance fo...

total sum of circle diagram
How can I get the total sum of a circle diagram which shows the single results in relation to the total sum to be displayed? Thanks in advance You mean a pie chart? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Schley" <Schley@discussions.microsoft.com> wrote in message news:6C5CE1C4-F4B5-45ED-ADAD-EC6405CC59D9@microsoft.com... > How can I get the total sum of a circle diagram which shows the single > results in relation to the total sum to be displayed? > > Tha...

How can I get the sum of a column with an arbitrary number of rows?
I am trying to get one cell to return the value of an entire column on another worksheet for which the last row is always changing. If I use: =SUM(Sheet1!$A$2:A25) Then the cel will be broken once I add a row 26 to Sheet 1. What I would like to do is create a sum of the column no matter how many rows there are. Thanks. "Mike F." <nospam@spamless.com> wrote in message news:4coomvgodg9lvcp66cp9bddcrnhov8ap00@4ax.com... > I am trying to get one cell to return the value of an entire column on > another worksheet for which the last row is always changing. If I use: >...

Sum of percentage in PIVOT
Hi All First of all THANK YOU for all the great posts... has been a great hel for me... Uptill now I have always been able to find as answer, bu this one I cant find. Hope you can help me out Question: I have made a PIVOT table and via the %-of totals I get the % per ro of the analyzed datas. What I want is a automatic SUM of th percentage, so that the final row adds up to 100%... Hope yo understand what I mean I know I can manually make another line besides the Pivot, but as m laziness I would like to have it done automatically.... Regards Martin (Denmark -- Message posted from http://www...

Calculating sum of two given times.
Is there a way to calculte the sum of two given times? For example, A1 says 08:00 and B1 says 17:00 then C1 will auto calculate "9". Also, if it can be done, because my time is always based on quarter hour, will the end result show a fraction? Example, A1is 07:45, B1 is 17:00, then C1 is 9 1/4. << Is there a way to calculte the sum of two given times? For example A1 says 08:00 and B1 says 17:00 then C1 will auto calculate "9".>> =(B1-A1)*24 and format Cell C1 as *_general_* << Also, if it can be done, because my time is always based on quarte hour...

Suppress Pivot table sub-totals when using "Sum of" XL2000
Hi, The Excel pivot table gives me almost all I want to organise some data. However, I get "subtotals" for every instance of a particular "row category". So if the category is "Green" and there are three instances of this in original data, pivot will sub-total these before listing the rows of category "RED". I only want totals for the whole table, not sub-totals "within" the table. Can I control this behaviour in the pivot table? Currently I do some "manual post-processing" using filters - but it is not dynamic and if th...

take sum of 6 different values and then * that answer by a %
I am trying to numbers from different cells and add them for a total and then multiply that number by a percent to get my answer. I can't figure out how to make this formula. Example c10+d10+e10+f10 +j10+m10 and then that total *by a percent. Thank you! =(c10+d10+e10+f10 +j10+m10)*10% -- Kind regards, Niek Otten Microsoft MVP - Excel "bic424" <bic424@discussions.microsoft.com> wrote in message news:CE16BB7B-9880-478B-9099-5309788A25BD@microsoft.com... >I am trying to numbers from different cells and add them for a total and >then > multiply that number b...

How to get total "conditional sum of cells" in a column?
Hi all, I have dollar amounts in one col, and status in another. I want the sum of those dollar amounts where the corresponding status cell is empty (blank). How do I do this? Thanks for any hints, cdj Status in Column A and dollar amounts in Column B: =SUMPRODUCT((A2:A100="")*B2:B100) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "sherifffruitfly" <sherifffruitfly@gmail.com> wrote in message news:bc08584b-1338-4b3f-8ab3-cc3e1602e581@n1g2000prb.go...

sum of a field filtered by date
HII have a table with commitments of customers on how much they will pay on what date. The fields are customer id, date and amount.Need to make a query to list out customer id, total payable till current date. I tried using sum function in total row in design grid, but it gives me some wierd result. other functions like avg, max and min work fine.also where can i specify that records with future dates are not to be included in the total?Thanks for any helpRamesh ...

Customizing dynamic chart to include sum of files pr. day?
Hi world, This is my log (20k) <http://img2.freeimagehosting.net/uploads/ 32373110dd.png> I have been trying to adapt a dynamic chart tutorial from Jon Peltier into a Excel filetransfer log. Dates along the x-axis and the sum of files transferred per day along the y-axis. Only problem is that I cant figure out how to plot the sum of total files transferred pr. date. In my log I have multiple entries for the day 23-11-2007, but instead of a total file count for the whole day Excel only use the last entry (A16). This is my named ranges: AllDates: <=Data!$A$1:INDEX(Data!$A:$A;MATCH(...

Sum of values for a month
Simple query that's probably been asked a million times before so it might be easier just to point me in the direction of a website that deals with Excel functions for dimwits. But my query is this.... I have a simple spreadsheet with the following columns : Date (A), Description (B), Income (C), Expense (D), Balance (E). I want a summary of it all at the bottom of the sheet so that for a row entry of "April 2008" it will look to see all the income entries in April 2008 and total them up. Similarly in the next column on the same row there should be a total for expenses fo...

excel charting formulas
I need help with creating a formula. i have 2 columns in my data i need to sum in my chart, 1 column is the sales reps name the other column is staus. when a project i work on becomes an order i type the word "order" in the status column, but i cant get the 2 columns to add up in my chart. what im trying to do is every time a given sales reps name appears in one column and the word "order" appears in the other column in the same row. i need to have a sum of how many times this appears, this gives me the amount of orders any one sales rep has generated. any help wou...

sum of cells
Hi, In Cell AB10 I need a formula to sum the following cells inthe same line. The cells are: D, F, H, J, L, N, P, R, T, V, X, Z Is there a way to do this other than using: =d10+f10+h10........+z10 (sum of every other cell!!) =SUMPRODUCT(--(MOD(COLUMN(D2:Z2),2)=0),D2:Z2) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Khalil handal" <khhandal@yahoo.com> wrote in message news:up25YVaXIHA.280@TK2MSFTNGP04.phx.gbl... > Hi, > In Cell AB10 I need a formula to sum the following cells inthe same line. > The cells ar...

sum of only green cells
How do I sum e.g. only green cells in my worksheet, when I color the cells in two different colors, green and red? Hi see: http://www.xldynamic.com/source/xld.ColourCounter.html and http://www.cpearson.com/excel/colors.htm not possible without VBA -- Regards Frank Kabel Frankfurt, Germany Jan Caesar wrote: > How do I sum e.g. only green cells in my worksheet, when I color the > cells in two different colors, green and red? try Sub addcolorcells() For Each c In Selection If c.Interior.ColorIndex = 4 Then ms = ms + c Next MsgBox ms End Sub -- Don Guillett SalesAid Software donald...