Pivot table question

[bwlytkr]

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 Cd	Cmc Cd	PWO Category	Job Nbr	WC Name	District Nm	Wa Cd	Actual Reported Hours
FL	BRVC	POLE	PWO5L3841	TITUSVILLE	FL NORTH	END	0
FL	BRVC	POLE	PWO5L3841	TITUSVILLE	FL NORTH	PLAC	1.75
FL	BRVC	POLE	PWO5L3841	TITUSVILLE	FL NORTH	REMO	0.75
FL	BRVC	POLE	PWO5L3841	TITUSVILLE	FL NORTH	TRNSFR	4
FL	BRVC	POLE	PWO6N7004	TITUSVILLE	FL NORTH	END	0
FL	BRVC	POLE	PWO6N7004	TITUSVILLE	FL NORTH	PLAC	2.5
FL	BRVC	POLE	PWO6N7004	TITUSVILLE	FL NORTH	REMO	2.5
FL	BRVC	POLE	PWO6N7004	TITUSVILLE	FL NORTH	SPL	2
FL	BRVC	POLE	PWO6N7004	TITUSVILLE	FL NORTH	TRNSFR	5.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>

 

[bbotzong]

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.

 

[bwlytkr]

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 Labels	Count of Wa Cd	Sum of Actual Reported Hours
FL	8910	26467.5
(blank)
Grand Total	8910	26467.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>