 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2007Use subtotal function on two columns
Hi there Here's our problem. We have a table like this: 58 5'630.00 58 997.00 58 489.00 58 385.44 57 2'244.00 57 2'228.00 57 1'451.24 57 576.00 57 0.00 56 2'752.00 56 1'644.00 56 544.58 56 472.31 55 927.00 54 3'068.00 54 1'868.00 What we want to have, is a table with the number of occurences of a particular number in the 1st colum and the sum of the relevant numbers in the 2nd colum. In our case: Number Occurences Sum 58 4 7'501.44 57 5 6'499.24 56 4 ... We found out how to make one, or the other, using the "Subtotal" menu item, but were not able to combine the two steps. It is very important to have the # of occurences and the sum on the same line, since we are using this data to build a graph. Thanks for your help!  SignatureThomas Guignard Central Library Swiss Federal Institute of Technology, Lausanne Thomas I would build another table with the first number in the first column (Say A) 58 57 56 55 54 and then alongside use a COUNTIF for the count of these and alongside that a SUMIF to sum the totals against each one SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS web: www.nickhodge.co.uk blog (non tech): www.nickhodge.co.uk/blog > Hi there > [quoted text clipped - 32 lines] > > Thanks for your help! List your unique numbers (58, 57, 56 etc) let's say in column F starting in F2 with appropriate headings in F1:H1. Then in G2 enter this formula: =COUNTIF(A:A,F2) and this one in H2: =SUMIF(A:A,F2,B:B) Copy these two formulae down to cover the numbers in column F. Hope this helps. Pete > Hi there > [quoted text clipped - 37 lines] > Central Library > Swiss Federal Institute of Technology, Lausanne Hi and thanks for your replies, This is a nice idea, the problem is that I was merely proposing an example, my list is way longer than that. Is there a way to extract the unique numbers? In SQL, my request would have been completed very easily, using the GROUP BY function. How come there is no GROUP possibility in Excel? Thanks again for your help. Pete_UK a écrit : > List your unique numbers (58, 57, 56 etc) let's say in column F > starting in F2 with appropriate headings in F1:H1. Then in G2 enter [quoted text clipped - 9 lines] > > Hope this helps. SignatureThomas Guignard Central Library Swiss Federal Institute of Technology, Lausanne To obtain unique numbers, all you need to do is highlight the data plus the heading (you must have a heading), then Data | Filter | Advanced filter. In the pop-up you should click on Unique Records only and Copy to Another location, and specify where you want the list to start - F1 in my example, as the header is produced as part of it. Click OK and you have your unique list. Then you can follow the rest of it. Hope this helps. Pete > Hi and thanks for your replies, > [quoted text clipped - 27 lines] > Central Library > Swiss Federal Institute of Technology, Lausanne Hi Thomas Insert a new row 1 and give titles to both columns Mark columns A and B. Data>Pivot Table>Next>Layout Drag your field name for Column A to the Row Area Drag the field name for column B to the Data Area>double click label>choose Sum Drag the field name for column B again to the Data Area>double click label>choose Count >OK>Finish On the PT report that is created, drag the Data label to Total and your Sum and Count will appear side by side. Click any cell on the PT>F11 and you will get a Chart SignatureRegards Roger Govier > Hi there > [quoted text clipped - 32 lines] > > Thanks for your help! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.