Group Summing Pivot Table Results

[gadgetic]

Hi

I have some sales data, I use Pivot Tables to organize the data into countries. So I have a column of countries with a column of sales for each country. Now I would like to sum up the sales into various groups, namely:

1. Germany
2. Europe
3. Non Europe

I can do this manually by using

=GETPIVOTDATA("GBP",$A$3,"Country","Germany")

for example

I can then have string of these commands to make up Europe

=GETPIVOTDATA("GBP",$A$3,"Country","Austria")+GETPIVOTDATA("GBP",$A$3,"Country","Belgium")+GETPIVOTDATA("GBP",$A$3,"Country","Croatia")+GETPIVOTDATA("GBP",$A$3,"Country","Cyprus")+GETPIVOTDATA("GBP",$A$3,"Country","Czech Republic")+GETPIVOTDATA("GBP",$A$3,"Country","Denmark")+GETPIVOTDATA("GBP",$A$3,"Country","Estonia")+GETPIVOTDATA("GBP",$A$3,"Country","Finland")+GETPIVOTDATA("GBP",$A$3,"Country","France")+GETPIVOTDATA("GBP",$A$3,"Country","Gibraltar")+GETPIVOTDATA("GBP",$A$3,"Country","Greece")+GETPIVOTDATA("GBP",$A$3,"Country","Iceland")+GETPIVOTDATA("GBP",$A$3,"Country","Ireland")+GETPIVOTDATA("GBP",$A$3,"Country","Italy")+GETPIVOTDATA("GBP",$A$3,"Country","Latvia")+GETPIVOTDATA("GBP",$A$3,"Country","Lithuania")+GETPIVOTDATA("GBP",$A$3,"Country","Malta")+GETPIVOTDATA("GBP",$A$3,"Country","Netherlands")+GETPIVOTDATA("GBP",$A$3,"Country","Poland")+GETPIVOTDATA("GBP",$A$3,"Country","Portugal")+GETPIVOTDATA("GBP",$A$3,"Country","Spain")+GETPIVOTDATA("GBP",$A$3,"Country","Sweden")+GETPIVOTDATA("GBP",$A$3,"Country","United Kingdom")

etc etc

However, the problem I have is, with every new set of data, not all countries are present, or some new ones are introduced. So how can I make a setup so that I create group of countries called Europe for example, then it will look if the country is present or not and sum up the data?

Any ideas?

Many Thanks

Gurpreet

 

[Sinon]

I think adding the group information to your source data might help. I would create a lookup table with each European country and the group I want it to be in (Germany or Europe). You can get a list of all countries in Europe from Google and put them all in the "Europe" category except for Germany. Once you have the table, use a VLOOKUP or INDEX/MATCH (whichever you prefer) in your source table to return the proper group in a new column. Something like:
=IFERROR(VLOOKUP(Country,Lookup table,2,FALSE),"Non Europe")

This will put them all in their respective groups and you can use that in your pivot table. Then, if you still need it, you can use GETPIVOTDATA to get the proper group as you will only have 3 (Germany, Europe and Non Europe)

 

[gadgetic]

Hi Sinon

That seems perfectly logical. I will try that. Thanks for the quick response.