ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Best way to subtotal this data? (https://www.excelbanter.com/excel-worksheet-functions/181557-best-way-subtotal-data.html)

Jonathan
Best way to subtotal this data?
 
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?

Jonathan
Best way to subtotal this data?
 
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.



"Jonathan" wrote:

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?

Ron Coderre
Best way to subtotal this data?
 
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)



"Jonathan" wrote in message
...
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.



"Jonathan" wrote:

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?

Pete_UK
Best way to subtotal this data?
 
In a separate sheet list all the people and all the groups that they could
be in, like this:

Person 1 Group A
Person 1 Group B
Person 1 Group C
Person 2 Group A
Person 2 Group B

and so on. Then put this formula in C1:

=SUMPRODUCT((Sheet1!A$1:A$1000=A1)*(Sheet1!B$1:B$1 000=B1)*(Sheet1!C$1:C$1000))

and copy it down as required.

Hope this helps.

Pete

"Jonathan" wrote in message
...
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.



"Jonathan" wrote:

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?



Jonathan
Best way to subtotal this data?
 
There is are a bunch of different subgroups within each group, so doing it
this way for some reason doesn't include them all. Instead of 650.5, I'm
getting 46 for my first person.

"Ron Coderre" wrote:

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)



"Jonathan" wrote in message
...
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.



"Jonathan" wrote:

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?

Jonathan
Best way to subtotal this data?
 
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).

"Ron Coderre" wrote:

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)



"Jonathan" wrote in message
...
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.



"Jonathan" wrote:

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?

Ron Coderre
Best way to subtotal this data?
 
Ok...let's start over...using your data in A1:D49

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/datam...ablereport.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" wrote in message
...
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).

"Ron Coderre" wrote:

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)



"Jonathan" wrote in message
...
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.



"Jonathan" wrote:

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?

Jonathan
Best way to subtotal this data?
 
Great, knew I was missing something.

Thanks!

"Ron Coderre" wrote:

Ok...let's start over...using your data in A1:D49

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/datam...ablereport.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" wrote in message
...
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).

"Ron Coderre" wrote:

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)



"Jonathan" wrote in message
...
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.



"Jonathan" wrote:

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?

Ron Coderre
Best way to subtotal this data?
 
You're very welcome, Jonathan..... Glad to help.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Jonathan" wrote in message
...
Great, knew I was missing something.

Thanks!

"Ron Coderre" wrote:

Ok...let's start over...using your data in A1:D49

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/datam...ablereport.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" wrote in message
...
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).

"Ron Coderre" wrote:

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)



"Jonathan" wrote in message
...
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.



"Jonathan" wrote:

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?


All times are GMT +1. The time now is 10:33 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com