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?

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

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

Rob H
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.
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 ?
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.

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

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