Excel Pivot Table: Calculate Sum & Count of Same Field

Often you may want to calculate the sum and the count of the same field in a pivot table in Excel.

You can easily do this by dragging the same field into the Values box twice when creating a pivot table.

The following example shows exactly how to do so.

Example: Calculate Sum & Count of Same Field in Excel Pivot Table

Suppose we have the following dataset in Excel that shows the sales of various products:

Now suppose we insert the following pivot table to summarize the sum of sales by product:

Now suppose we would also like to summarize the count of sales for each product.

To do so, we can simply drag the Sales value in the PivotTable Fields panel to the Values box again:

Next, click on the dropdown arrow next to Sum of Sales2 and click on Value Field Settings:

In the new window that appears, click Count and then click OK:

A new field will be added to the pivot table that shows the count of sales:

Feel free to click on the new field name and change the name to “Count of Sales”:

Excel pivot table sum and count same field

The pivot table now shows the sum of sales and the count of sales for each product.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

Excel: How to Filter Top 10 Values in Pivot Table
Excel: How to Sort Pivot Table by Grand Total
Excel: How to Calculate the Difference Between Two Pivot Tables

Leave a Reply

Your email address will not be published.