SUMPRODUCT or SUMIFS .What is the better approach to solve the problem

[Kumarxl]

Friends ,
I tried my best , but need help.
Table 1 contains time sheet information . hrs ,project id, manager/resource names(2 levels only)
I have been asked to get total hrs grouped by managers

Managers list : Randy, Srini, Kavin

Table 1

[TABLE="class: cms_table, width: 387"]
<tbody>[TR]
[TD]EMP_HRS[/TD]
[TD]PROJECTID[/TD]
[TD]L1 NAME[/TD]
[TD]L2 NAME[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID12345[/TD]
[TD]Jeff[/TD]
[TD]Randy[/TD]
[TD]Functional[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ID12345[/TD]
[TD]Randy[/TD]
[TD]Jag[/TD]
[TD]Functional[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]ID12345[/TD]
[TD]Randy[/TD]
[TD]Jag[/TD]
[TD]Functional[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]ID12345[/TD]
[TD]Srini[/TD]
[TD]Chandra[/TD]
[TD]Functional[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]ID12345[/TD]
[TD]Randy[/TD]
[TD]Srini[/TD]
[TD]Functional[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]ID12345[/TD]
[TD]Randy[/TD]
[TD]Srini[/TD]
[TD]Functional[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ID12345[/TD]
[TD]Mike[/TD]
[TD]Kavin[/TD]
[TD]Functional[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ID12345[/TD]
[TD]Mike[/TD]
[TD]Kavin[/TD]
[TD]Functional[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]ID89090[/TD]
[TD]Randy[/TD]
[TD]Jag[/TD]
[TD]Functional[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]ID89090[/TD]
[TD]Srini[/TD]
[TD]Chandra[/TD]
[TD]Functional[/TD]
[/TR]
</tbody>[/TABLE]



=IF((COUNTIFS(Table1!$D$2:$D11,$B$1:$D$1,Table1!$B$2:$B$11,$A2,Table1!$D$2:$D$11,B$1))=0,
SUMPRODUCT(--(Table1!$B$2:$B$11=$A2),--(Table1!$E$2:$E$11="Functional"),--(ISNA(MATCH(Table1!$D$2:$D$11,$B$1:$C$1,0))),--(Table1!$C$2:$C$11=B$1),Table1!$A$2:$A$11),SUMPRODUCT((Table1!$B$2:$B$11=$A2)*(Table1!$E$2:$E$11="Functional")*((Table1!$D$2:$D$11=B$1)+(Table1!$C$2:$C$11=B$1)),Table1!$A$2:$A$11))

From the above formula , I get the below values .
Actual (from above formulae)
[TABLE="class: cms_table, width: 269"]
<tbody>[TR]
[TD]ProjectID[/TD]
[TD]Randy[/TD]
[TD]Srini[/TD]
[TD]Kavin[/TD]
[/TR]
[TR]
[TD]ID12345[/TD]
[TD]57[/TD]
[TD]74[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


However, Randy's 57 hrs is not correct because it is adding srini's 44 hrs. I need help how to exclude Srini's hrs from Randy. Expected:

[TABLE="class: cms_table, width: 269"]
<tbody>[TR]
[TD]ProjectID[/TD]
[TD]Randy[/TD]
[TD]Srini[/TD]
[TD]Kavin[/TD]
[/TR]
[TR]
[TD]ID12345[/TD]
[TD]13[/TD]
[TD]74[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]



Any help is appreciated.
First of all, Am i moving in right direction with the above formula? or Do we have better approach to solve?

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	EMP_HRS	PROJECTID	L1 NAME	L2 NAME	Type					ProjectID	Randy	Srini	Kavin
2	1	ID12345	Jeff	Randy	Functional					ID12345	13	74	4
3	2	ID12345	Randy	Jag	Functional
4	10	ID12345	Randy	Jag	Functional
5	30	ID12345	Srini	Chandra	Functional
6	14	ID12345	Randy	Srini	Functional
7	30	ID12345	Randy	Srini	Functional
8	2	ID12345	Mike	Kavin	Functional
9	2	ID12345	Mike	Kavin	Functional
10	25	ID89090	Randy	Jag	Functional
11	30	ID89090	Srini	Chandra	Functional
Data

Cell Formulas
Range	Formula
K2	=SUMPRODUCT(($B$2:$B$11=$J2)*(($C$2:$C$11=K$1)+($D$2:$D$11=K$1))*($D$2:$D$11<>L$1)*($D$2:$D$11<>M$1),($A$2:$A$11))
L2	=SUMPRODUCT(($B$2:$B$11=$J2)*(($C$2:$C$11=L$1)+($D$2:$D$11=L$1))*($D$2:$D$11<>M$1)*($D$2:$D$11<>K$1),($A$2:$A$11))
M2	=SUMPRODUCT(($B$2:$B$11=$J2)*(($C$2:$C$11=M$1)+($D$2:$D$11=M$1))*($D$2:$D$11<>K$1)*($D$2:$D$11<>L$1),($A$2:$A$11))

 

[Kumarxl]

Thanks for quick Response . Challenge is I have 100+ managers in the header range(manager name. e.g $k$1 - $AZ$1) .Is there way to exclude a value from the range instead of using l1,m1. etc
($D$2:$D$11<>L$1)*($D$2:$D$11<>M$1)

 

[Fluff]

Whilst it may be possible, I don't know how.
But I don't understand why you want rows 3 & 4 to Randy rather than the L2name, but rows 6 & 7 are added to the L2name & not Randy

 

[Kumarxl]

SUM Rows 3 & 4 to Randy , because L2Name Jag is not a manager .
SUM Rows 6 & 7 to Srini , because l2name Srini is a manager .

Basically we wants hrs grouped by the managers (Manager hrs+reportee) at the lowest level .

In this case Randy is Sr Manager, Srini is manager and Jag is Sr analyst,Chandra is analyst . Jag and srini are reporting to Randy and Chandra is reporting to Srini.
So Srini's hrs and Chandra's hrs will added to Srini's column( as srini is the manager and present in the header range) .Jags hrs and Randy's hrs will be added to Randy's column .

Confusing

 

[Fluff]

Confusing

Definitely
Hopefully one of the formula experts will stop by & help.