Simple SUMIF, I think...

[steph]

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...

 

[Don Guillett]

Have you looked in the help index for SUMIF?

 

[Tim M]

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]

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?

 

[Don Guillett]

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

 

[steph]

Don, yes that's where I started. But I couldn't find assistance for my
specific problem. See my reply to Tim earlier.

 

[Tim M]

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]

Tim, that did the trick. Thanks for thinking outside the SUMIF box!!