Ok...let's start over...using your data in A1
49
From the Excel Main Menu: <Data><Pivot Table>
Use: Excel..Click [Next]
Select your data..Click [Next]
Click the [Layout] button
ROW:
Drag the Person field here
Drag the Group field here
COLUMN: (leave this area blank)
DATA:
Drag the Hours field here
If it doesn't list as Sum of Hours
....dbl-click it and set it to Sum
Click [OK]
Select where you want the Pivot Table...Click [Finish].
That will list the Sum of Hours for each combination of Person and Group
In your example, the total is 650.5
To refresh the Pivot Table, just right click it and select Refresh Data
Pivot Table Links:
http://www.nickhodge.co.uk/gui/datamenu/pivottablereport.htm
http://www.contextures.com/tiptech.html
Does that help?
Post back if you have more questions.
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
Jonathan said:
Here is the actual data for the first person:
PERSON GROUP SUBGROUP HOURS
Person 1 Group A A 1.5
Person 1 Group A B 1.0
Person 1 Group A B 0.5
Person 1 Group A C 29.5
Person 1 Group A C 7.5
Person 1 Group A C 1.0
Person 1 Group A C 32.0
Person 1 Group A C 36.0
Person 1 Group A C 32.0
Person 1 Group A C 27.0
Person 1 Group A C 6.0
Person 1 Group A C 12.0
Person 1 Group A C 8.0
Person 1 Group A C 24.0
Person 1 Group A C 10.0
Person 1 Group A C 8.5
Person 1 Group A C 9.0
Person 1 Group A C 16.0
Person 1 Group A C 10.0
Person 1 Group A C 10.0
Person 1 Group A C 14.0
Person 1 Group A C 16.0
Person 1 Group A C 17.0
Person 1 Group A C 3.5
Person 1 Group A C 25.5
Person 1 Group A C 2.0
Person 1 Group A C 1.0
Person 1 Group A C 14.0
Person 1 Group A C 12.0
Person 1 Group A C 1.0
Person 1 Group A C 1.0
Person 1 Group A D 1.5
Person 1 Group A E 30.0
Person 1 Group A E 4.0
Person 1 Group A E 17.0
Person 1 Group A E 23.5
Person 1 Group A E 25.0
Person 1 Group A E -
Person 1 Group A E 24.0
Person 1 Group A E 26.0
Person 1 Group A E 20.0
Person 1 Group A F 21.0
Person 1 Group A F 20.0
Person 1 Group A F 20.0
Person 1 Group A F 20.0
Person 1 Group A F 10.0
It should total 650.5 or something like that, but I'm getting 46 in the
pivot table for total hours. Some people will fit into multiple
groups,
this
person just happened to be in one. For some reason, even when I add
the
subgroup field in the pivot table, it doesn't count for cells that have
other
cells in the same group with the same number. (IE as you can see,
subgroup F
has 3 entries where the hours are 20. Only one of these is counted
instead
of all three).
:
Your Pivot Table layout should be:
ROW:
Name_Field
Group_Field
DATA:
Sum of Hours_Field
That will total hours by Person by Group.
Or..you could drag the Group_Field to the column headings.
Does that help?
Post back if you have more questions.
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
Just an update--I've tried a pivot table and this isn't seeming to
count
multiple entries that are the same from the field:
Here's an example of one person:
Person 1 Group A 1
Person 1 Group A 3
Person 1 Group A 5
Person 1 Group A 76
Person 1 Group A 5
Person 1 Group A 3
Person 1 Group A 78
Person 1 Group A 9
Person 1 Group A 4
Person 1 Group A 6
Person 1 Group A 3
Person 1 Group A 3
Person 1 Group A 3
Person 1 Group A 4
For the instances where there are multiple cells with the same
number
(ie
3,
4, etc.), it only counts these once...how can I have it total these
cells.
:
Hello all,
I have a worksheet with a bunch of data that I need to extract
certain
statistics from. I need to show the total number of hours charged
by
each
person to each group they worked in for the last year. So, I need
to
sort by
person, then by group, then by total number of hours in each. Each
person
may or may not have any hours in any given group. Any suggestions?
