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

Kumarxl

New Member
Joined
Mar 10, 2019
Messages
4
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

EMP_HRSPROJECTIDL1 NAMEL2 NAMEType
1ID12345JeffRandyFunctional
2ID12345RandyJagFunctional
10ID12345RandyJagFunctional
30ID12345SriniChandraFunctional
14ID12345RandySriniFunctional
30ID12345RandySriniFunctional
2ID12345MikeKavinFunctional
2ID12345MikeKavinFunctional
25ID89090RandyJagFunctional
30ID89090SriniChandraFunctional

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

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

ProjectIDRandySriniKavin
ID1234513744

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

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
How about

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">EMP_HRS</td><td style=";">PROJECTID</td><td style=";">L1 NAME</td><td style=";">L2 NAME</td><td style=";">Type</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">ProjectID</td><td style=";">Randy</td><td style=";">Srini</td><td style=";">Kavin</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">1</td><td style=";">ID12345</td><td style=";">Jeff</td><td style=";">Randy</td><td style=";">Functional</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">ID12345</td><td style="text-align: right;;">13</td><td style="text-align: right;;">74</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">2</td><td style=";">ID12345</td><td style=";">Randy</td><td style=";">Jag</td><td style=";">Functional</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">10</td><td style=";">ID12345</td><td style=";">Randy</td><td style=";">Jag</td><td style=";">Functional</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">30</td><td style=";">ID12345</td><td style=";">Srini</td><td style=";">Chandra</td><td style=";">Functional</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">14</td><td style=";">ID12345</td><td style=";">Randy</td><td style=";">Srini</td><td style=";">Functional</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">30</td><td style=";">ID12345</td><td style=";">Randy</td><td style=";">Srini</td><td style=";">Functional</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">2</td><td style=";">ID12345</td><td style=";">Mike</td><td style=";">Kavin</td><td style=";">Functional</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">2</td><td style=";">ID12345</td><td style=";">Mike</td><td style=";">Kavin</td><td style=";">Functional</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">25</td><td style=";">ID89090</td><td style=";">Randy</td><td style=";">Jag</td><td style=";">Functional</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">30</td><td style=";">ID89090</td><td style=";">Srini</td><td style=";">Chandra</td><td style=";">Functional</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Data</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">$B$2:$B$11=$J2</font>)*(<font color="Red">(<font color="Green">$C$2:$C$11=K$1</font>)+(<font color="Green">$D$2:$D$11=K$1</font>)</font>)*(<font color="Red">$D$2:$D$11<>L$1</font>)*(<font color="Red">$D$2:$D$11<>M$1</font>),(<font color="Red">$A$2:$A$11</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">$B$2:$B$11=$J2</font>)*(<font color="Red">(<font color="Green">$C$2:$C$11=L$1</font>)+(<font color="Green">$D$2:$D$11=L$1</font>)</font>)*(<font color="Red">$D$2:$D$11<>M$1</font>)*(<font color="Red">$D$2:$D$11<>K$1</font>),(<font color="Red">$A$2:$A$11</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">$B$2:$B$11=$J2</font>)*(<font color="Red">(<font color="Green">$C$2:$C$11=M$1</font>)+(<font color="Green">$D$2:$D$11=M$1</font>)</font>)*(<font color="Red">$D$2:$D$11<>K$1</font>)*(<font color="Red">$D$2:$D$11<>L$1</font>),(<font color="Red">$A$2:$A$11</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Kumarxl

New Member
Joined
Mar 10, 2019
Messages
4
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 10, 2019
Messages
4
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,614
Messages
5,523,906
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top