Exceljet

Quick, clean, and to the point

Pivot table two-way sum

Pivot tables are an easy way to quickly sum unique values in a data set, and can easily be adapted to perform a two-way sum. In the example shown above, a pivot table is used to sum The Qty field for unique combinations of City and Size, based on data in the range B5:D17, defined as an Excel Table.

Fields

The pivot table shown is based on three fields: City, Size, and Qty. The City field is added as a Row field, and the Size field is added as a Column field. The Qty field is is added as a Value field:

Pivot table two-way sum Field list

The Qty field in the Values area is configured to Sum:

Pivot table two-way sum value field settings

By default the Pivot Table automatically sums values in the Qty field, so there is no need to change the calculation in this case.

Steps

  1. Define data as an Excel Table (optional)
  2. Create a pivot table based on table (or data)
  3. Add City field to the Rows area
  4. Add Size field to the Columns area
  5. Add Qty field to the Values area

Notes

  1. When a numeric field is added as a Value field, Excel the field is automatically summed.

Pivot Table Training

If you use Excel, but don't know how to use Pivot Tables, you're missing out...wasting time trying to do things that a Pivot Table can do for you automatically. Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this powerful tool. With a small investment, Pivot Tables will pay you back again and again. See details here.