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

Department	Name	Role	Monthly Cost	Jan-15	Feb-15	Mar-15	Apr-15	May-15
IT	John	Analyst	£60	1.00	1.00	1.00	1.00	1.00
Projects	Harry	Coordinator	£50	0.00	0.00	0.50	0.75	1.00
Operations	Jane	Engineer	£80	1.00	1.00	1.00	1.00	1.00
Projects	Kevin	Project Manager	£100	0.00	0.50	1.00	1.00	0.50

<tbody>
</tbody>

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 -

Date	Cost	Number of People
Jan-15	£0.00
Feb-15	£50.00	0.5
Mar-15	£125.00	1.5
Apr-15	£137.50	1.75
May-15	£100.00	1.5

<tbody>
</tbody>

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