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
Trisha RamirezAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

goto A3 then
Pivot Table Tools / Calculations / Fileds, Items & Sets / Calculated Item

and create a sum of the Row labels you want to subtotal

see example

Regards
EE20150318-1.xlsx
0
 
Rob HensonFinance AnalystCommented:
You might be able to do it with a calculated field within the Pivot Table. If you were to upload some data, might be able to work on it.

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="B",Source="G",Source="H"),1,0)
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
0
 
Rory ArchibaldCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Trisha RamirezAuthor Commented:
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 ?
0
 
Rob HensonFinance AnalystCommented:
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
0
 
Rob HensonFinance AnalystCommented:
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
0
 
Trisha RamirezAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.