Sumif of Sumif perhaps?

[Fred]

I have a rather large spreadsheet of Project Manager data that I want
to use to generate a pivot report from, however in the Pivot I want to
create %'ages of the subtotals. All that I understand as there are
several threads that give me sufficienf pointers.

What i'm stuggling with is the SUMIF side of it. My data layout (or a
cut-down version of it) is as follows

A B C D -----------> O
P----> AA
Project Project Team Forecast Forecast Sumif
Sumif
Manager Name Name Days - Jan Days- Dec Jan Dec

Each project can have any of up to 20 different teams working on it
A Project can be managed by any 1 of 15 Project Managers
Each Project Manager can be managing up to 15 projects at any one time

What I want to produce in my pivot is a report showing the %'age of a
working month each Project manager is spending on each of his projects.
But I only want to include the forecast time for the Project
Management team, not any of the other 20 or so teams that may be
present.

My attempt at the sumif went as follows
=IF($B2="Proj - Project Management -
EU",SUMIF($A$2:$A$587,$A2,D$2$587),0)
in columns P thru AA for columns D thru O.

That worked, after a fashion, but included all the forecast data for
every team rather than just the Project Managers, which I (perhaps
optimistically) thought the IF would filter out for me.

Any help would be much appreciated.

Regards
Fred

 

[robert111]

i find that for complex multiple extraction, the sumproduct function i
better and easie

 

[Fred]

OK I have a sort of solution, using 2 additional columns for each month
in the original data,

In columns P -> AA I used a formula to get only the values for the
Project Managers forecasts
=IF(C2="Proj - Project Management - EU",D2,0)

and then in columns AB -> AM I used a SUMIF to calculate the %'age
allocated to each project for each month.
=IF(ISERROR(P2/SUMIF($A$2:$A$1000,$A2,P$2$1000)),0,(P2/SUMIF($A$2:$A$1000,$A2,P$2$1000)))

A B C D --------> O
P----> AA AB ---> AM
Project Project Team Forecast Forecast Sumif Sumif
% %
Manager Name Name Days-Jan Days-Dec Jan Dec
Jan Dec

And then simply pivoted the data to get the desired results.

Not the prettiest of solutions I admit, but it worked.

Can someone explain how Robert's suggestion to use SumProduct would
have been used in this instance as I don't follow the logic of
multiplying two text values to come up with something meaningful.

Regards
Fred