|
Simple 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... -- Thanks so much! |
Simple SUMIF, I think...
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) "steph" wrote: 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... -- Thanks so much! |
Simple SUMIF, I think...
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? -- Thanks so much! "Tim M" wrote: 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) "steph" wrote: 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... -- Thanks so much! |
Simple SUMIF, I think...
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 -- Don Guillett SalesAid Software "steph" wrote in message ... 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... -- Thanks so much! |
Simple SUMIF, I think...
Don, yes that's where I started. But I couldn't find assistance for my
specific problem. See my reply to Tim earlier. -- Thanks so much! "Don Guillett" wrote: Have you looked in the help index for SUMIF? -- Don Guillett SalesAid Software "steph" wrote in message ... 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... -- Thanks so much! |
Simple SUMIF, I think...
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. "steph" wrote: 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? -- Thanks so much! "Tim M" wrote: 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) "steph" wrote: 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... -- Thanks so much! |
Simple SUMIF, I think...
Tim, that did the trick. Thanks for thinking outside the SUMIF box!!
-- Thanks so much! "Tim M" wrote: 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. "steph" wrote: 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? -- Thanks so much! "Tim M" wrote: 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) "steph" wrote: 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... -- Thanks so much! |
| All times are GMT +1. The time now is 12:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com