Use SUMPRODUCT, along the lines of

=SUMPRODUCT(($A$1:$A$1000="Needed")*($B$1:$B$1000= "Java")*C$1:C$1000)

Put the Java. C++, etc down column H, and then use

=SUMPRODUCT(($A$1:$A$1000="Needed")*($B$1:$B$1000= $H2)*C$1:C$1000)

and you can copy the formula into a table, which will sum columns C, D, and
E.

HTH,
Bernie
MS Excel MVP


"xrbbaker" wrote in message
...
Yeah Bernie, you are lazy and I'm a VBA expert.

I've been getting closer by trying to create an interim table. I THINK I
can get things into a sub-set table by using the MATCH, INDEX and INDIRECT
functions. I use match to find the first occurence of "Needed", then keep
track of the found row in another cell, and use that row +1 as the
beginning
of the next range, using INDIRECT to build the range and INDEX to grab the
values. If I can make that "ungrouped" sub-set table, then I could use
Ed's
summing code to group/total it into one line.

It was actually my trying to explain the issue to you that helped me
envision this approach/attempt. Problem is it is all brute force and ugly
as
heck. I'm going to try to finish it off and see what it looks like, then
try
to digest your code. Yours would be much more cool. Once in a pivot
table
it would be pretty easy to stand it on its head. This will keep me busy
for
days. Thanks very much.



"Bernie Deitrick" wrote:

You could use a macro. This macro assumes that you have five columns of
data, with

Status What Jan Feb Mar

in A2:E2 as your headers, and that your data doesn't extend more than
10,000 rows, with
miscellaneous stuff mixed in. The basis is "Needed" in column A. (We
could write it to take the
exact number of rows into account, but I'm lazy.... ;-))

HTH,
Bernie
MS Excel MVP


Sub Macro1()
Dim mySht As Worksheet
Dim myPTS As Worksheet

Set mySht = ActiveSheet

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Pivot Source").Delete
Set myPTS = Worksheets.Add(Befo=Sheets(1))
myPTS.Name = "Pivot Source"

With mySht.Range("A2:E10000")
.AutoFilter Field:=1, Criteria1:="Needed"
.SpecialCells(xlCellTypeVisible).Copy myPTS.Range("A2")
.AutoFilter
End With

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & myPTS.Name & "'!R2C1:R10000C5").CreatePivotTable
TableDestination:="", TableName _
:="PivotTable1"

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Status")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Status").CurrentPage
= _
"Needed"
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("What")
.Orientation = xlRowField
.Position = 1
End With

ActiveSheet.PivotTables("PivotTable1").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Jan"), "Sum of Jan", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Feb"), "Sum of Feb", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Mar"), "Sum of Mar", xlSum
Range("B3").Select

With ActiveSheet.PivotTables("PivotTable1").DataPivotFi eld
.Orientation = xlColumnField
.Position = 1
End With
End Sub



"xrbbaker" wrote in message
...
Bernie,

Your Filter suggestion had me thinking the same way. Problem is the
data
isn't in perfect cube-like pivot format, and can't get that way. It
has
plenty of row gaps and column gaps. It has values mixed in that are
really
subsection headers. I don't know how many rows there will be. There
will be
a lot of managerial interraction with the worksheet as teams of people
play
"what if?" so it needs to keep the labels, gaps, subheaders, etc. As
they
are playing 'what if we replace this real person with a "needed"
placeholder', and vice-versa, they will want to see the results of
their
what-iffing then and there. No time for them to make a change, then
hand it
over to me to cut and paste together a pivot representation. There
could
possibly be hundreds of changes as they try to optimize the staffing
across
projects.

I think the only way this can be done, given the real-world context, is
to
(roughly)

1) loop down column A and collect the rows that contain "needed."
2) go to each row collected and read the value of column B. Build an
array
containing each unique value.
3) for every occurence of each unique value, sum the person-time needs
for
each month
4) spit out the results of needed skill and total person-time per
month for
that skill

Sounds hairy, but I'm sure it can be done. It's just beyond me.



"Bernie Deitrick" wrote:

Russ,

Then a pivot table will work if you just want the summary: I tried it
on your data set (including
blank rows between) and it worked fine. Create a header row in the
top row, select all the data
(including the header row), then use Data / Pivot Table, and click
through. Drag "Type" to the
Row
Field, and Jan, Feb, Mar to the data field, and set each to sum. Then
drag the data button to
the
column heading, and you should be done.

HTH,
Bernie
MS Excel MVP


"xrbbaker" wrote in message
...
Hey Bernie,

That might be ok for small numbers. Looking across 50-75 projects I
don't
know how many resources will be needed for each one. IN the end, I
expect
there may be a hundred or more needs and those need types should be
rolled up
into one entry. I need to show 1 line of C++ and the fact that
there must be
22.5 additional folks in Jan, vs the filter which might show 30 rows
of .75
people. Maybe if the filter can do some kind of grouping or
totalling???

"Bernie Deitrick" wrote:

Seems like you could just filter your entire sheet, and show just
the values of "Needed" in
column
A.

HTH,
Bernie
MS Excel MVP


"xrbbaker" wrote in message
...
I'm trying to do some resource forcasting and in the end display
what
resources are needed,by month. Through a simple VLOOKUP or by
using some of
Debra Dalgleish's Contextures TIPS, I can get the very first part
of it, but
that's about it. That is, walk down column A until it finds
"Needed" and
then return the associated needed skill. Then I hit a wall.

Can I get where I want to go without VBA? If VBA is required, I
can do some
basic VBA stuff by myself, but not this. Can anybody point me to
a site that
has something like this, or give me a jump start?

thanks very much - Russ

PROJECT 1 Jan Feb Mar
Mike cobol 1 1 1
Tom java 1 .5 .25
Needed C++ .5 .5 .5


PROJECT 2
Sally cobol 1 1 1
Kim .net .75 .75 .75
Needed Oracle .5 .5 .5

PROJECT 3
Needed Oracle .75


Desired results table...

Jan Feb Mar
Needed C++ .5 .5 .5
Needed Oracle 1.25 .5 .5