Selecting number of hours per subgroup

[The Fool on the Hill]

Dear Excel(lent) Users,

I have an excel sheet in which I keep track of the hours spent per person.

The following columns are interesting (the rest I won't bother you with).

Column A => Number
Column B => Name of the person
Column C => Name of department
Column D => Number of hours spent

Now I want to know how many hours were spent per department. I figure it has
something to do with sumproduct, but am not quite sure.

Thanks for helping me out !!

 

[bpeltzer]

You'll usually use sumproduct to deal with criteria in multiple fields. If
your only criterion is the department name, use can use the simpler sumif:
=sumif(c:c,"This Department",d:d). The arguments are where to look for the
required value, what value to look for, and what to add upon finding it. So
we're looking for "This Department" in the column of department names and
adding up the hours spent.

 

[Max]

Something like this should work in say, G2, copied down:
=sumproduct(($C$2:$C$100=F2)*$D$2:$D$100)
where F2 down contains the departments

 

[The Fool on the Hill]

Hello Bpeltzer and Max,

Unfortunately both don't work !

 

[Max]

Hello Bpeltzer and Max,
Unfortunately both don't work !

I'm surprised you say that this didn't work:
In G2: =sumproduct(($C$2:$C$100=F2)*$D$2:$D$100)
(you've adapted the ranges to suit?)

Maybe try wrap TRIM around col C values for more robust* matching:
In G2: =SUMPRODUCT((TRIM($C$2:$C$100)=F2)*$D$2:$D$100)
*there could be extraneous white spaces throwing the matching off
(you could wrap TRIM for F2 as well)

You also need to format G2 as time, custom: [h]:mm
if col D values are in time format

If the above doesn't work, that means probably col D's data aren't real time
values. Post some samples of col D (and col C) values

---

 

[The Fool on the Hill]

Nr. Name Dept Hrs Spent
1 Person A Dev 2
2 Person B Dev 3
3 Person C Test 4
4 Person D Dev 5
5 Person E Test 5

If you look at the above, I am looking for a formula, which would give me
the following:

Dept Hrs Spent
------------------------------
Dev 10
Test 9

Hope this helps !

 

[Max]

Here's a sample demonstrating that the earlier sumproduct works, and an
alternative to get both the uniques listing of depts and the total hrs spent
using pivot table:

http://www.freefilehosting.net/download/3c0md
Sumproduct n pivot options.xls

In the sheet: Source
The earlier sumproduct** solution is shown working properly in G2:G3
**In G2: =SUMPRODUCT(($C$2:$C$100=F2)*$D$2:$D$100)

If you are actually asking for a uniques list of depts to be produced
concurrently, then a pivot table is a fast n easy way to arrive at the
desired summary

Select any cell within the data, click Data > PivotTable ...
Click Next > Next
In step 3 of the wizard, click Layout
Drag n drop "Dept" within the ROW area
Drag n drop "Hrs Spent" within the DATA area
(It'll appear as "Sum of Hrs Spent")
Click OK > Finish. Incredible, but that's it.

Hop over to the pivot sheet (to the left)
and you'll see the desired results:
a. A unique listing of depts in the 1st col, & next to it,
b. The total hrs spent

 

[The Fool on the Hill]

Hey Max,

Great work thanks !

Here's a sample demonstrating that the earlier sumproduct works, and an
alternative to get both the uniques listing of depts and the total hrs spent
using pivot table:

http://www.freefilehosting.net/download/3c0md
Sumproduct n pivot options.xls

In the sheet: Source
The earlier sumproduct** solution is shown working properly in G2:G3
**In G2: =SUMPRODUCT(($C$2:$C$100=F2)*$D$2:$D$100)

If you are actually asking for a uniques list of depts to be produced
concurrently, then a pivot table is a fast n easy way to arrive at the
desired summary

Select any cell within the data, click Data > PivotTable ...
Click Next > Next
In step 3 of the wizard, click Layout
Drag n drop "Dept" within the ROW area
Drag n drop "Hrs Spent" within the DATA area
(It'll appear as "Sum of Hrs Spent")
Click OK > Finish. Incredible, but that's it.

Hop over to the pivot sheet (to the left)
and you'll see the desired results:
a. A unique listing of depts in the 1st col, & next to it,
b. The total hrs spent

 

[Max]

No prob, welcome. Albeit I'm still not sure why the expression didn't work
for you earlier, though ..