Avatar of Trisha Ramirez
Trisha Ramirez
 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
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Trisha Ramirez

8/22/2022 - Mon
Rob Henson

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 Archibald

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.

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.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Trisha Ramirez

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 ?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Rob Henson

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 Henson

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 Ramirez

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.
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.