# Summary of calcs for totals in pivot table

#### ammdumas

##### Active Member
Howdy,

Pivot Tables are a bit of a hole in my excel expertise. I know how to use them, but not very well.

The sample sheet below shows a summary row of calculations based on counts of TRUEs and FALSEs in the data. It actually counts 1s and 0 in columns J through M because I do some error catching as well. The Percentage Complete is based on those counts AND the weighted numbers shown above each summary header.

Works great, except now the boss want to see those summary calculations separated by Area (column B). My first thought is pivot tables! Question is how do I get those summary calcs into a pivot table for each area? I saw a post about inserting calculated fields into a pivot table (Formulas...Calculated Field) but I don't seem to have that function available to me (I run Excel 2002).

Any thoughts?
ModelTrack.xls
ABCDEFGHIJKLM
5Equipment
6As of Date:08/02/2007
7%506095100
8TypePercentage CompleteTotalsDayPreliminaryVendor PreliminaryVerifiedFinalErrors
9EQUI61.821988110514330
10 0000
11TagAreaClassTypePreliminaryVendor PreliminaryVerifiedFinalErrors
12/26-AR-00126MECHARTRUETRUEFALSEFALSE 0100
13/26-BH-90326MECHBHTRUETRUEFALSEFALSE 0100
14/26-CH-00126PLTWCHTRUETRUEFALSEFALSE 0100
15/26-CR-00126MECHCRTRUETRUEFALSEFALSE 0100
16/26-CV-00126MECHCVTRUETRUEFALSEFALSE 0100
17/26-EF-90426MECHEFTRUETRUEFALSEFALSE 0100
18/26-FE-00126MECHFETRUETRUEFALSEFALSE 0100
Equipment

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### ammdumas

##### Active Member
Ok. Figured out part of the problem. The created pivot table below shows exactly what I wanted to create (The Grand total at the bottom confirms the numbers are correct) EXCEPT for the Percentage Complete.

If you look at the original post, the Grand Total number should be 61.82. I think this has to do with the fact that the formula I created is SUM OF Percentage complete, not Percentage Complete. That formula needs to be calcuated on the colculate Counts and Sums in the pivot table, not the source data.

How would I do that, short of doing the formula outside of the pivot table?
ModelTrack summary by area.xls
ABCDEFG
3Data
4AreaCount of TagSum of PSum of VPSum of VSum of IFCSum of Percentage Complete
52612012002.307692308
628633001.964285714
730100013.333333333
831361715401.908602151
932548190272.453703704
1033312001.717171717
114126210051.454033771
1242110001.19047619
1347440001.063829787
145148480000.980392157
1558770000.862068966
16Grand Total198110514331.609890833
Sheet1

Replies
0
Views
1K
Replies
48
Views
2K
Replies
2
Views
626
Replies
0
Views
342
Replies
3
Views
638

Threads
1,191,025
Messages
5,984,201
Members
439,877
Latest member
kellylet

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back