# Pivot summing mutliple cols

#### JohnKauffman

##### New Member
I’m having problems understanding a PT behavior. I made a simple example which works. Then I added three cols and it fails. I think there is something I don’t understand & need help with what specific topic to study.

Simple case (I understand)
 Name Service Q1 Amy dry \$3 Amy iron \$2 Amy wash \$1 Bob wash \$3 Cass dry \$1 Cass iron \$3

<tbody>
</tbody>
I want to know total spent by each person on each service for the quarter
PT of Row=name col=service value=Q1
No problem.

Problematic Case
Now I add cols for Q3…Q4 as follows:
 Name Service Q1 Q2 Q3 Q4 Amy dry \$3 \$1 \$0 \$1 Amy iron \$2 \$0 \$3 \$3 Amy wash \$1 \$3 \$3 \$3 Bob wash \$3 \$3 \$1 \$3 Cass dry \$1 \$2 \$3 \$2 Cass iron \$3 \$1 \$2 \$1

<tbody>
</tbody>
I want to know the total spent by each person on each service for the total year. The result should look like this:
Name wash dry…
Amy \$10 \$5
Bob \$10 \$0

PT of row = name col = service values=Q1,Q2,Q3,Q4.
results (partial):
 Column Labels dry iron Row Labels Sum of Q1 Sum of Q2 Sum of Q3 Sum of Q4 Sum of Q1 Sum of Q2.. Amy 3 1 0 1 2 0 Bob Cass 1 2 3 2 3 1 Grand Total 4 3 3 3 5 1

<colgroup><col><col><col span="5"></colgroup><tbody>
</tbody>

Here is the behavior I don’t understand:
1 - When I add the Qx to Values an item automatically appears in cols called “(Sigma) Values”.
2 - The result is a table where the Qx are sub-cols under each service rather than just one col for Services and the value = total of 4 Qs added together.

My best guess is that I have to do something to define in values that I want the multiple fields summed, not set as sub-cols.

Does anyone know the keywords for this situation for me to search for an explanation / lesson / example?
I have sample book with data but don't see a place to attach.

Thanks.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
If I were tackling this, I would have made a sumproduct table, but maybe you are trying to learn pivot tables.

Use a "calculated field" in your pivot table to sum the Quarters... place that calculated field in the Values section of the pivot table

With the data arranged the way that you specified, danzon has the answer that will work for you. Because Q1, Q2, Q3 & Q4 are all separate columns the pivot table keeps them separate. Consider if instead they were columns like Spent, Items, shirts & Pants. You would not want the pivot table to add them.

If your data table looked like:

 name service quarter amount Amy dry 1 3 Amy dry 2 1 Amy dry 4 1 Amy iron 1 2 Amy iron 3 3 Amy iron 4 3 Bob wash 1 3 Bob wash 2 3

<tbody>
</tbody>

Then you can use quarter as a column in the pivot table and the table will automatically produce a sum.

Thanks, Danzon. I got too wrapped around Qx being logically related. That is true for humans but not the way PT sees it. I should just think of them as independent cols. & use your technique. (Worked)
Thanks, Par. I agree that is the way the data should have been organized. I can write a VBA to revise it. Thanks for endorsement of Danzon answer

Replies
3
Views
157
Replies
1
Views
115
Replies
3
Views
206
Replies
5
Views
511
Replies
5
Views
340

1,214,659
Messages
6,120,781
Members
448,992
Latest member
prabhuk279

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back