How to do regional aggregation based on country data in Excel

[comper]

<embed id="xunlei_com_thunder_helper_plugin_d462f475-c18e-46be-bd10-327458d045bd" type="application/thunder_download_plugin" height="0" width="0">HI, everyone, I working on some country group numbers.
Given the aggregation of the country often change, so it will be convenient to have a flexible setting on this, and automatically sum the country data to group data. But I tried sum product and index, vlookup.
Seems doesn't work, and the difficulty is mainly on a. three sheets are from different places. b. how to map one aggregated region to the country regions in Excel.

Attached, please help me, thanks!.

Sheet 1: Region_mapping, e.g. EUG4 =France +Germany +Italy +UK.
Sheet 2: Data for these countries (this table is always from exogenous source, so change directly on this table might not be convenient).
Sheet 3, I want to get the summation of these 4 countries as the number for EUG4.

For example, for EUG4
Sumproduct (--(Aggeregation_region="EUG4"),--(Euro_region..=…),). I don't how to map from data to country and then to aggregation further together.




Thanks!

Comper<embed id="xunlei_com_thunder_helper_plugin_d462f475-c18e-46be-bd10-327458d045bd" type="application/thunder_download_plugin" height="0" width="0"><embed id="xunlei_com_thunder_helper_plugin_d462f475-c18e-46be-bd10-327458d045bd" type="application/thunder_download_plugin" height="0" width="0"><embed id="xunlei_com_thunder_helper_plugin_d462f475-c18e-46be-bd10-327458d045bd" type="application/thunder_download_plugin" height="0" width="0">

 

[comper]

aggregation	country
OE5	Norway
OE5	Switzerland
OE5
OE5
EUG4	France
EUG4	Germany (until 1990 former territory of the FRG)
EUG4	Italy
EUG4	United Kingdom

<colgroup><col><col></colgroup><tbody>
</tbody>

	2010	2011	2012
Belgium	17,755	17,750	14,521
Bulgaria	6,120	6,518	6,286
Czech Republic	14,762	14,985	14,403
Denmark	10,573	12,025	12,292
Germany (until 1990 former territory of the FRG)	252,462	265,025	254,499
Estonia	1,388	1,561	1,599
Ireland	8,221	7,470	7,419
Greece	25,256	16,809	16,486
Spain	146,194	142,323	133,368
France	164,325	168,242	156,449
Croatia	4,547	4,375	4,145
Italy	149,248	127,681	111,785
Cyprus	1,066	923	880
Latvia	2,561	2,646	2,616
Lithuania	2,292	2,320	2,438
Luxembourg	574	650	1,047
Hungary	11,329	10,534	9,181
Netherlands	33,782	33,593	32,654
Austria	13,914	14,475	14,118
Poland	82,218	89,734	89,013
Portugal	12,881	12,673	11,180
Romania	12,096	11,858	12,673
Slovenia	2,288	2,177	1,849
Slovakia	5,198	4,906	5,073
Finland	25,156	23,732	21,928
Sweden	32,732	33,402	30,370
United Kingdom	137,753	:	:
Liechtenstein	:	:	:
Norway	16,344	16,131	16,983
Switzerland	9,550	9,912	9,987

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

	2010	2011	2012
OE5	???
EUG4	???

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

 

[healey21]

Welcome to the Forum, try something like this. Use a helper column and assign a number to the countries then you can simply use a SUMIF to gather the year information for that regions example shown below:

Excel Workbook
	A	B	C	D	E
1	Country	2010	2011	2012	Helper
2	Belgium	17,755	17,750	14,521
3	Bulgaria	6,120	6,518	6,286
4	Czech Republic	14,762	14,985	14,403
5	Denmark	10,573	12,025	12,292
6	Germany (until 1990 former territory of the FRG)	252,462	265,025	254,499	1
7	Estonia	1,388	1,561	1,599
8	Ireland	8,221	7,470	7,419
9	Greece	25,256	16,809	16,486
10	Spain	146,194	142,323	133,368
11	France	164,325	168,242	156,449	1
12	Croatia	4,547	4,375	4,145
13	Italy	149,248	127,681	111,785	1
14	Cyprus	1,066	923	880
15	Latvia	2,561	2,646	2,616
16	Lithuania	2,292	2,320	2,438
17	Luxembourg	574	650	1,047
18	Hungary	11,329	10,534	9,181
19	Netherlands	33,782	33,593	32,654
20	Austria	13,914	14,475	14,118
21	Poland	82,218	89,734	89,013
22	Portugal	12,881	12,673	11,180
23	Romania	12,096	11,858	12,673
24	Slovenia	2,288	2,177	1,849
25	Slovakia	5,198	4,906	5,073
26	Finland	25,156	23,732	21,928
27	Sweden	32,732	33,402	30,370
28	United Kingdom	137,753	:	:	1
29	Liechtenstein	:	:	:
30	Norway	16,344	16,131	16,983
31	Switzerland	9,550	9,912	9,987
Sheet1

Then SUMIF table results

Excel Workbook
	F	G	H	I	J
1			2010	2011	2012
2
3		OE5	???
4	EUG4 =France +Germany +Italy +UK	EUG4	7001	6859	6624
Sheet1

 

[comper]

many thanks. It works like this. But still, it need I put a new flag to the regional aggregation. So the problem seems to be: how I can express the condition to judge whether a country belonging to a group or not, then sum the number of the country to the group.

Any hints on the best method???