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
<tbody>
</tbody>
=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)
<tbody>
</tbody>
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:
<tbody>
</tbody>
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?
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
EMP_HRS | PROJECTID | L1 NAME | L2 NAME | Type |
1 | ID12345 | Jeff | Randy | Functional |
2 | ID12345 | Randy | Jag | Functional |
10 | ID12345 | Randy | Jag | Functional |
30 | ID12345 | Srini | Chandra | Functional |
14 | ID12345 | Randy | Srini | Functional |
30 | ID12345 | Randy | Srini | Functional |
2 | ID12345 | Mike | Kavin | Functional |
2 | ID12345 | Mike | Kavin | Functional |
25 | ID89090 | Randy | Jag | Functional |
30 | ID89090 | Srini | Chandra | Functional |
<tbody>
</tbody>
=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)
ProjectID | Randy | Srini | Kavin |
ID12345 | 57 | 74 | 4 |
<tbody>
</tbody>
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:
ProjectID | Randy | Srini | Kavin |
ID12345 | 13 | 74 | 4 |
<tbody>
</tbody>
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?