Pivot summing mutliple cols

JohnKauffman

New Member
Joined
Nov 1, 2012
Messages
36
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 LabelsSum of Q1Sum of Q2Sum of Q3Sum of Q4Sum of Q1Sum of Q2..
Amy310120
Bob
Cass1
23231
Grand Total4
33351

<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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
If I were tackling this, I would have made a sumproduct table, but maybe you are trying to learn pivot tables.
 

danzon

Well-known Member
Joined
Oct 11, 2010
Messages
655
Use a "calculated field" in your pivot table to sum the Quarters... place that calculated field in the Values section of the pivot table

ScreenShot1233.gif
 

par60056

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
  1. 2011
  2. 2010
Platform
  1. Windows
  2. MacOS
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:

nameservicequarteramount
Amydry13
Amydry21
Amydry41
Amyiron12
Amyiron33
Amyiron43
Bobwash13
Bobwash23

<tbody>
</tbody>

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

JohnKauffman

New Member
Joined
Nov 1, 2012
Messages
36
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top