Calculated Items In Pivot Tables
When you've created a pivot table in Excel, you can easily add two fields together and list the result in new field - one not present in your original data source.
1. When you begin the pivot, add the main field from the pivot table you will review data for and a second field which has entries to be tracked for each of the main field items, to the Row area of the field list. In this example, we want to review the groceries sent to three different cities.
2. Next in the pivot table, select a cell with one of the items you want to run a calculation for, and then go the Analyze menu, go to Fields, Items, & Sets . . . Calculated Item.
3. Give a name to a new field, and then select the field that has the items that you want to add up. In the formula box, simply list the items separated by a plus sign. You should see the field whose items are referenced in the formula in the title bar of the dialog box.
4. Finally, pull down a third field into the values area, and set it so a count of that field is generated. The new field, in this example 'Total_Fruit' will show just the sum of the formula you entered.
Naturally, a calculated item can show the result of any number of formulas.