Excel – Divide Sum of Field By the Sum of Another In Pivot Table Excel

microsoft-excel-2010pivot table

How can I conduct the following calculation in pivot table? Sum the Total Hours and divide it by the sum of the Ticket Count for each Year-Month. Here's a snapshot of my spreadsheet.

enter image description here

The output would be something like:

enter image description here

If I use the Average function in the Pivot Table option, it would just average the total count of the data occurrence, not the sum of the Ticket Count.

Best Answer

In Excel, after creating a Pivot Table, on the PivotTable Analyze tab, go to Fields, Items, & Sets, then Insert Calculated Field, and add a new field with the following Formula:

`=SUM('Total Hours')/SUM('Ticket Count')`

Click OK and exit the Insert Calculated Field window. Open up the Field List, using the newly created field as Values as shown here:

enter image description here

You will need to do some extra cleaning up of the values to make it look exactly like your example output, but that would be outside the scope of this question.

Related Question