Add values from multiple columns in pivot table

Question

I have a created a pivot table from an Excel spreadsheet which has many columns and many rows. Here is my requirement.

The Pivot Table has

Row Labels --> Individual Names
Column Labels --> Types of Products

Now I have 4 regions like AP, EMEA, CALA, & US in the Excel spreadsheet.

I need to get the value of = Sum of (AP + EMEA + CALA + AP), for each type of Product for the respective individual name.

For example,

Clarke would have sold Type 1 Product, 4 Nos in AP, 10 in EMEA, 4 in CALA, 7 in US
       would have sold Type 2 product, 12 Nos in AP, 16 in EMEA, 8 in CALA, 5 in US

I need pivot table, which looks like

                   Type 1         Type 2       Type 3       Type 4
Clarke                 25             41            0            0
Marsh                  11             20           12            6

How do I get this?

No statistical content here. General "how to use a spreadsheet" questions would swamp CV. — Nick Cox

pnuts pnuts · Accepted Answer · 2013-12-17T00:31:17

Like this:

SO17391057 example

Grand Total for rows and columns is optional and other Types/Names etc may be added to suit into the source data without other configuration changes except (i) possibly the range will need extending (PivotTable Tools > Data - Change Data Source) and (ii) the PT will need to be refreshed (right-click on the data area and left-click Refresh).

Add values from multiple columns in pivot table

1 Answers