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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.