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]

[TABLE="class: grid, width: 457"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]aggregation[/TD]
[TD]country[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OE5[/TD]
[TD]Norway[/TD]
[/TR]
[TR]
[TD]OE5[/TD]
[TD]Switzerland[/TD]
[/TR]
[TR]
[TD]OE5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OE5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EUG4[/TD]
[TD]France[/TD]
[/TR]
[TR]
[TD]EUG4[/TD]
[TD]Germany (until 1990 former territory of the FRG)[/TD]
[/TR]
[TR]
[TD]EUG4[/TD]
[TD]Italy[/TD]
[/TR]
[TR]
[TD]EUG4[/TD]
[TD]United Kingdom[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 313"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]2012[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OE5[/TD]
[TD]???[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EUG4[/TD]
[TD]???[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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???