Selecting number of hours per subgroup
 

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

Selecting number of hours per subgroup
 

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.

"The Fool on the Hill" wrote:


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

Selecting number of hours per subgroup
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"The Fool on the Hill" wrote:
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 !!

Selecting number of hours per subgroup
 

Hello Bpeltzer and Max,

Unfortunately both don't work !

"The Fool on the Hill" wrote:


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

Selecting number of hours per subgroup
 

"The Fool on the Hill" wrote:
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Selecting number of hours per subgroup
 

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 !



"The Fool on the Hill" wrote:

Hello Bpeltzer and Max,

Unfortunately both don't work !

"The Fool on the Hill" wrote:


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

Selecting number of hours per subgroup
 

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"The Fool on the Hill" wrote:
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 !

Selecting number of hours per subgroup
 

Hey Max,

Great work thanks !

"Max" wrote:

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"The Fool on the Hill" wrote:
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 !

Selecting number of hours per subgroup
 

No prob, welcome. Albeit I'm still not sure why the expression didn't work
for you earlier, though ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"The Fool on the Hill" wrote in
message ...
Hey Max,

Great work thanks !