Pivot summing mutliple cols

[JohnKauffman]

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.

 

[oldbrewer]

If I were tackling this, I would have made a sumproduct table, but maybe you are trying to learn pivot tables.

 

[danzon]

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

 

[par60056]

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.

 

[JohnKauffman]

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