Add a combined totals row in a pivot, whilst only showing select data

J

JamieL

New Member
Joined
Mar 20, 2018
Messages
3
Hello,

I have a question regarding grouping some information in a Pivot. I have added some example data in a mini sheet that hopefully adds some clarity.

I have data in the sheet which is split by three regions. I can pivot the data to show all regions, but what I would like to do is to have one region visible, and then have an overall value of all regions showing at the same time.

On the sheet I have created a mock design to show how I would like it to look (Cells H18 - L25). In this example I am showing Europe as the visible region, then 'Worldwide' as a new value showing the total of all regions.

Any advice on how I could do this would be much appreciated.

Book1
CDEFGHIJKLMN
1Current Pivot
2
3CarRegionYearCount of CarYear
4BMWEurope2020CarRegion20202021Grand Total
5BMWEurope2020BMWEurope213
6BMWEurope2021BMWJapan235
7TeslaEurope2020BMWN.America527
8TeslaEurope2021FordEurope448
9FordEurope2020FordN.America336
10FordEurope2020TeslaEurope112
11FordEurope2020TeslaJapan33
12FordEurope2020TeslaN.America279
13FordEurope2021Grand Total192443
14FordEurope2021
15FordEurope2021
16FordEurope2021How I would like it to look
17BMWN.America2020
18BMWN.America2020Count of CarYear
19BMWN.America2020CarRegion20202021Grand Total
20BMWN.America2020BMWEurope213
21BMWN.America2020BMWWorldwide9615Worldwide is a total of all regions
22BMWN.America2021FordEurope448
23BMWN.America2021FordWorldwide7714
24TeslaN.America2020TeslaEurope112
25TeslaN.America2020TeslaWorldwide31114
26TeslaN.America2021
27TeslaN.America2021Grand Total192443
28TeslaN.America2021
29TeslaN.America2021
30TeslaN.America2021
31TeslaN.America2021
32TeslaN.America2021
33FordN.America2020
34FordN.America2020
35FordN.America2020
36FordN.America2021
37FordN.America2021
38FordN.America2021
39BMWJapan2020
40BMWJapan2020
41BMWJapan2021
42BMWJapan2021
43BMWJapan2021
44TeslaJapan2021
45TeslaJapan2021
46TeslaJapan2021
Sheet1
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
If you load your data to the data model and use Power Pivot, you will get an option "Include Filtered Items in Totals" which will do what you want.
Top Five Report
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

N
  • Question
2-step Dynamic Ranges and Charts
Replies
0
Views
152
Nick Loughton
N
A
  • Question
Find and Replace Value in List Named Range Reference
Replies
0
Views
291
arembr01
A
B
  • Question
VBA/Formula to only run lookup if a cells value is a match
Replies
0
Views
340
Benjo83
B
DEllis
  • Question
Pivot Table or something else
Replies
2
Views
182
DEllis
DEllis
C
  • Question
Confused over pivot table calculations :-(
Replies
1
Views
250
number kruncher
N

Forum statistics

Threads
1,214,792
Messages
6,121,612
Members
449,038
Latest member
apwr

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top