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

#### Kumarxl

##### New Member
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_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?

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

#### Kumarxl

##### New Member
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
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
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

##### MrExcel MVP, Moderator
Confusing
Definitely
Hopefully one of the formula experts will stop by & help.

Replies
6
Views
463
Replies
3
Views
38
Replies
6
Views
75
Replies
10
Views
93
Replies
2
Views
27