Pivot table question

bwlytkr

Board Regular
Joined
Jun 8, 2012
Messages
102
I am trying to build a pivot table that will sum up the total number of Transfer items in the Wa Cd column and total the # of actual reported hours for the entire Job Number. If I filter on the transfer item it will only total the number of transfers. Is this possible in a pivot table? Below is a sample of the data. As you can see the job numbers have hours on multiple lines but only 1 transfer item for each job. Any help would be greatly appreciated. Thanks John



State CdCmc CdPWO CategoryJob NbrWC NameDistrict NmWa CdActual Reported Hours
FLBRVC POLE PWO5L3841TITUSVILLE FL NORTH END0
FLBRVC POLE PWO5L3841TITUSVILLE FL NORTH PLAC1.75
FLBRVC POLE PWO5L3841TITUSVILLE FL NORTH REMO0.75
FLBRVC POLE PWO5L3841TITUSVILLE FL NORTH TRNSFR4
FLBRVC POLE PWO6N7004TITUSVILLE FL NORTH END0
FLBRVC POLE PWO6N7004TITUSVILLE FL NORTH PLAC2.5
FLBRVC POLE PWO6N7004TITUSVILLE FL NORTH REMO2.5
FLBRVC POLE PWO6N7004TITUSVILLE FL NORTH SPL2
FLBRVC POLE PWO6N7004TITUSVILLE FL NORTH TRNSFR5.5
<colgroup><col width="64" style="width: 48pt;" span="2"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;"> <col width="64" style="width: 48pt;"> <col width="56" style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048;"> <col width="137" style="width: 103pt; mso-width-source: userset; mso-width-alt: 5010;"> <tbody> </tbody>
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

bbotzong

New Member
Joined
Dec 17, 2003
Messages
40
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
You mean like this?

Sum of Actual Reported Hours Column Labels
Row Labels END PLAC REMO SPL TRNSFR Grand Total
PWO5L3841 - 1.8 0.8 4.0 6.5
PWO6N7004 - 2.5 2.5 2.0 5.5 12.5
Grand Total - 4.3 3.3 2.0 9.5 19.0

If so, COlumns is Wa Cd, Rows is Job Nbr, and Values is sum of Actual Reported Hours.
 
Last edited:

bwlytkr

Board Regular
Joined
Jun 8, 2012
Messages
102
Thanks but I am trying to put it in a summary format since I have regional data with 1000's of lines of data. Is it possible to do something similar to below but only pull the transfer data in the Wa Cd column but give me the total hrs for the job

Row LabelsCount of Wa CdSum of Actual Reported Hours
FL891026467.5
(blank)
Grand Total891026467.5
<colgroup><col width="120" style="width: 90pt; mso-width-source: userset; mso-width-alt: 4388;"> <col width="131" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4790;"> <col width="246" style="width: 185pt; mso-width-source: userset; mso-width-alt: 8996;"> <tbody> </tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,109,149
Messages
5,527,098
Members
409,745
Latest member
CharlesAlsop

This Week's Hot Topics

Top