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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

F
  • Question Question
Showing details of Pivot table from Grand total, including Row filtering
Replies
0
Views
155
Francois2France
F
G
  • Question Question
Timeline on Pivot Table not working
Replies
1
Views
199
Sergius
S
N
  • Question Question
2-step Dynamic Ranges and Charts
Replies
0
Views
1K
Nick Loughton
N
K
  • Question Question
Formula to find Data and Corresponding date and return total sales and total units per item
Replies
4
Views
185
RoryA
RoryA
H
  • Question Question
VBA Pivot Table Dynamic Range & Only One Selection per Row
Replies
3
Views
264
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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