SUMPRODUCT Advice Please

[beaker66]

Hi,

I have a table of People/Roles, how much each costs per month, and what proportion of their time that person will spend in the Role each month (1 = 100%, 0.5 = 50% etc).

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Department
[/TD]
[TD]Name
[/TD]
[TD]Role
[/TD]
[TD]Monthly Cost
[/TD]
[TD]Jan-15
[/TD]
[TD]Feb-15
[/TD]
[TD]Mar-15
[/TD]
[TD]Apr-15
[/TD]
[TD]May-15
[/TD]
[/TR]
[TR]
[TD]IT
[/TD]
[TD]John
[/TD]
[TD]Analyst
[/TD]
[TD]£60
[/TD]
[TD]1.00
[/TD]
[TD]1.00
[/TD]
[TD]1.00
[/TD]
[TD]1.00
[/TD]
[TD]1.00
[/TD]
[/TR]
[TR]
[TD]Projects
[/TD]
[TD]Harry
[/TD]
[TD]Coordinator
[/TD]
[TD]£50
[/TD]
[TD]0.00
[/TD]
[TD]0.00
[/TD]
[TD]0.50
[/TD]
[TD]0.75
[/TD]
[TD]1.00
[/TD]
[/TR]
[TR]
[TD]Operations
[/TD]
[TD]Jane
[/TD]
[TD]Engineer
[/TD]
[TD]£80
[/TD]
[TD]1.00
[/TD]
[TD]1.00
[/TD]
[TD]1.00
[/TD]
[TD]1.00
[/TD]
[TD]1.00
[/TD]
[/TR]
[TR]
[TD]Projects
[/TD]
[TD]Kevin
[/TD]
[TD]Project Manager
[/TD]
[TD]£100
[/TD]
[TD]0.00
[/TD]
[TD]0.50
[/TD]
[TD]1.00
[/TD]
[TD]1.00
[/TD]
[TD]0.50
[/TD]
[/TR]
</tbody>[/TABLE]











I want to create a second, dynamic table which shows (based on user-entered selection criteria) for each month, the total cost of and the total number people.

If the User selects (i.e. types the following into the three cells at the top of the sheet) ...

(A1) Department = "Pro"
(A2) Name = ""
(A3) Role = ""

...we'd expect the resulting table underneath (A5:C10) to be correctly populated with -

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Cost
[/TD]
[TD]Number of People
[/TD]
[/TR]
[TR]
[TD]Jan-15
[/TD]
[TD]£0.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb-15
[/TD]
[TD]£50.00
[/TD]
[TD]0.5
[/TD]
[/TR]
[TR]
[TD]Mar-15
[/TD]
[TD]£125.00
[/TD]
[TD]1.5
[/TD]
[/TR]
[TR]
[TD]Apr-15
[/TD]
[TD]£137.50
[/TD]
[TD]1.75
[/TD]
[/TR]
[TR]
[TD]May-15
[/TD]
[TD]£100.00
[/TD]
[TD]1.5
[/TD]
[/TR]
</tbody>[/TABLE]











I don't think Pivot Tables will do what I want.

I'm half-way there in that if I apply the following ...

=SUM(OFFSET(Row_of_Dates_from_1st_table,1,MATCH(A6,Res_Plan,0)-1,COUNTA(Column_of_Names_from_1st_table)))

..to the first table I can work out the total Number of People per month.

Then...

=SUMPRODUCT(ISNUMBER(SEARCH(A1,Department_column))*ISNUMBER(SEARCH(A2,Name_column))*ISNUMBER(SEARCH(A3,Role_column))) w

...will get me the number of rows that actually match the selection criteria....

However - how can I marry the two to produce a table that summarises the costs and the number of people based on the selection criteria entered?

Any good ideas much appreciated.
Thanks for your time and help.

 

[LxQ]

I had the People/Roles table in A13:I17

for the salary I used this formula:
=SUMPRODUCT(--(A$14:A$17=$A$1), --(B$14:B$17=$A$2),--(C$14:C$17=$A$3), --(D$14:D$17))

for number of people:
=SUMPRODUCT(--(A$14:A$17=$A$1), --(B$14:B$17=$A$2),--(C$14:C$17=$A$3), --(INDIRECT(ADDRESS(14,MATCH(A6,A$13:I$13,0))):INDIRECT(ADDRESS(17,MATCH(A6,A$13:I$13,0)))))