Pulling Multiple Totals, Averages, and Counts
 

I have a rather large table with the following columns:

MACHINE ID#
MACHINE NAME
TIME DOWN
LOST CAPACITY

("Machine ID" is discrete, "Machine Name" is not. In other words, Machine 1
and Machine 2 can both be "Jeep").

I'd like to:
Sort by Machine ID
Get a subtotal of Time Down
Get a subtotal of Lost Capacity
Get a COUNT of how many entries there are for each ID
Listed by Machine Name and ID.

In other words:

RAW DATA

ID NAME TIME CAPACITY
1 Jeep 4 20
1 Jeep 5 25
1 Jeep 6 30
2 Jeep 10 120
2 Jeep 18 150
3 Vette 40 28
3 Vette 42 28


REPORT

ID NAME TIME CAPACITY n
1 Jeep 15 75 3
2 Jeep 28 270 2
3 Vette 82 56 2

The best I've been able to do is:

ID NAME TIME CAPACITY
1 SUBTOTAL 15 75
1 COUNT 3 3 3
2 SUBTOTAL 28 270
2 COUNT 2 2 2

I've figured out how to make the sums pretty with Pivot Tables, but the
counts?

I have 300 machine ID's and over 5000 line item entries, so easy and elegant
is the order of the day!!

Thanks

Pulling Multiple Totals, Averages, and Counts
 

Create a pivot table with ID and Name in the Row area, and Time and
Capacity in the Data area, as Sum.
Turn off the Subtotals for the ID field.
Add another copy of the Name field to the data area, where it will
become Count of Name. You can change that heading to something
different, e.g. Entries.


RJB wrote:
I have a rather large table with the following columns:

MACHINE ID#
MACHINE NAME
TIME DOWN
LOST CAPACITY

("Machine ID" is discrete, "Machine Name" is not. In other words, Machine 1
and Machine 2 can both be "Jeep").

I'd like to:
Sort by Machine ID
Get a subtotal of Time Down
Get a subtotal of Lost Capacity
Get a COUNT of how many entries there are for each ID
Listed by Machine Name and ID.

In other words:

RAW DATA

ID NAME TIME CAPACITY
1 Jeep 4 20
1 Jeep 5 25
1 Jeep 6 30
2 Jeep 10 120
2 Jeep 18 150
3 Vette 40 28
3 Vette 42 28


REPORT

ID NAME TIME CAPACITY n
1 Jeep 15 75 3
2 Jeep 28 270 2
3 Vette 82 56 2

The best I've been able to do is:

ID NAME TIME CAPACITY
1 SUBTOTAL 15 75
1 COUNT 3 3 3
2 SUBTOTAL 28 270
2 COUNT 2 2 2

I've figured out how to make the sums pretty with Pivot Tables, but the
counts?

I have 300 machine ID's and over 5000 line item entries, so easy and elegant
is the order of the day!!

Thanks


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html