 |
 | 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 / Worksheet Functions / June 2006Simple SUMIF, I think...
I have 2 columns of data. In col B I have a % value; in col C I have various project names. I need a formula that will show me the total of col B for each project name in col C. There is no data after col C so I could use those cols for the results. col B col C 20% proj A 80% proj A 10% proj B 30% proj B 70% proj B 100% proj C So my results should be 100% proj A; 110% proj B; 100% proj C. Obviously the 110% row will require more research on my part...  SignatureThanks so much! Have you looked in the help index for SUMIF? SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com >I have 2 columns of data. In col B I have a % value; in col C I have >various [quoted text clipped - 12 lines] > So my results should be 100% proj A; 110% proj B; 100% proj C. Obviously > the 110% row will require more research on my part... Don, yes that's where I started. But I couldn't find assistance for my specific problem. See my reply to Tim earlier. SignatureThanks so much! > Have you looked in the help index for SUMIF? > [quoted text clipped - 14 lines] > > So my results should be 100% proj A; 110% proj B; 100% proj C. Obviously > > the 110% row will require more research on my part... this might be what you are looking for. you could then just copy this formula and change the proj A to proj B, proj C etc. You will have to format these cells as % as well. =SUMIF($C$1:$C$6,"proj A",$B$1:$B$6) > I have 2 columns of data. In col B I have a % value; in col C I have various > project names. I need a formula that will show me the total of col B for [quoted text clipped - 11 lines] > So my results should be 100% proj A; 110% proj B; 100% proj C. Obviously > the 110% row will require more research on my part... Tim, that does work but my list has over 200 projects in it. I was hoping to avoid having to put the project name itself in the formula. Basically, I'm trying to validate that all projects in the list total 100%, no less and no more. Any other ideas? SignatureThanks so much! > this might be what you are looking for. you could then just copy this > formula and change the proj A to proj B, proj C etc. You will have to format [quoted text clipped - 16 lines] > > So my results should be 100% proj A; 110% proj B; 100% proj C. Obviously > > the 110% row will require more research on my part... I assume the Project names are intermingled in the list. What I might do is to sort according to Project names. Then I would go 'data'...'subtotals' and at each change in project name sum the %, this should give you a total % for each project and you can note which ones do not add up to 100. > Tim, that does work but my list has over 200 projects in it. I was hoping to > avoid having to put the project name itself in the formula. Basically, I'm [quoted text clipped - 21 lines] > > > So my results should be 100% proj A; 110% proj B; 100% proj C. Obviously > > > the 110% row will require more research on my part... Tim, that did the trick. Thanks for thinking outside the SUMIF box!! SignatureThanks so much! > I assume the Project names are intermingled in the list. What I might do is > to sort according to Project names. Then I would go 'data'...'subtotals' and [quoted text clipped - 26 lines] > > > > So my results should be 100% proj A; 110% proj B; 100% proj C. Obviously > > > > the 110% row will require more research on my part... Set it up like this with a list in col D and col E formatted as % percent Project 20% A a 100% 80% A b 110% 10% B c 100% 30% B 70% B 100% C use this Sub verifytotal() For Each c In Range("d2:d" & _ Cells(Rows.Count, "d").End(xlUp).Row) c.Offset(, 1) = Application.SumIf(Columns(3), c, Columns(2)) Next End Sub SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com >I have 2 columns of data. In col B I have a % value; in col C I have >various [quoted text clipped - 12 lines] > So my results should be 100% proj A; 110% proj B; 100% proj C. Obviously > the 110% row will require more research on my part... |
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
|
©2009 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.