asked on
Pivot Table Sub Totals
Is there any way to create a row in a pivot table that will contain subtotals of a specific subset of a row label. For instance if I had a row label named source that had the following values: A, B, G, H, #, ?. Can I create a row that subtotals the various efforts mailed based on whether the source is A, B, G, H but not include the counts for #, ?. The following is what I currently have and the bold section is what I would like added.
Source ----- Eff1 Mailed ------ Eff2 Mailed
A---------------- 10-------------------- 10
B----------------- 5---------------------- 4
G------------- 148-------------------- 140
H------------- 658-------------------- 524
TOTAL----- 821-------------------- 678
$------------ 1247------------------ 1056
? -------------- 52--------------------- 47
Source ----- Eff1 Mailed ------ Eff2 Mailed
A---------------- 10-------------------- 10
B----------------- 5---------------------- 4
G------------- 148-------------------- 140
H------------- 658-------------------- 524
TOTAL----- 821-------------------- 678
$------------ 1247------------------ 1056
? -------------- 52--------------------- 47
Microsoft ExcelMicrosoft Office
Last Comment
Trisha Ramirez
You could use a calculated Item (not Field) or you could also just group the A,B,G,H entries so you have an additional row field that aggregates those items.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I guess I should of added this portion as well. Unfortunately the values are not always the same for the source field. We may have A, B, G, H one time and then have A, B, J, L,M,X the next time. Would a calculated item still work? The final table should list all of the individual sources with the quantity mailed for effort 1, effort 2 and so on. As well as a value under source that says total conversion which will be a sum of all the individual sources listed above it excluding $ and ?. Then it will include the individual quantities for sources $ and ?
Apologies, I meant Item rather than field.
I had forgotten about the Group option now available in Pivot Tables; I have 2010 but still have mindset of 2003 when it comes to Pivots; much learning needed.
Thanks
Rob H
I had forgotten about the Group option now available in Pivot Tables; I have 2010 but still have mindset of 2003 when it comes to Pivots; much learning needed.
Thanks
Rob H
So would it be safe to say you want a sum of everything excluding $ and ? as a Subtotal and then a Grand Total for everything? Or will the inclusions be specific on each occasion?
If the latter, you could create a list of Included values and then using option 1 in my earlier suggestion:
=IF(ISERROR(MATCH(Source, List, 0)),0,1)
This will return 0 or 1 based on finding the source value in the list; 0 = not found.
Thanks
Rob H
If the latter, you could create a list of Included values and then using option 1 in my earlier suggestion:
=IF(ISERROR(MATCH(Source, List, 0)),0,1)
This will return 0 or 1 based on finding the source value in the list; 0 = not found.
Thanks
Rob H
ASKER
It is safe to assume I want a sum of everything excluding $ and ?; however I still need to list the quantities for $ and ? as well. I have attached a sample report of what the final results should look like. The highlighted field is what I am trying to add.
Sample-Report.xls
Sample-Report.xls
However, probably easier with one of these two options:
1) Include a field in the data evaluating whether Source should be included:
=IF(OR(Source="A",Source="
Include this field in the pivot and Subtotal on that.
2) Apply a filter to the table to hide the $ & ? entries, the total will then only be those needed. I suspect you want to see the $ and ? rows though so this won't be suitable.
Thanks
Rob H