Counties, continents and number of schools. What formula should I use?

[pirduh]

I have countries and in which continet they belong in different cells. Then I also have the name of a country and a number of top schools in every country in different cells. I have to find out how many top schools are in every continent. What formula should I use?

[pre]

Country	        Continent
United Kingdom 	Europe
United States 	North America
Switzerland 	Europe
Canada 	        North America
Australia 	Australia
Hong Kong 	Asia
Japan 	        Asia
Singapore 	Asia
France 	        Europe
Denmark 	Europe
China 	        Asia
Korea, South 	Asia
Germany 	Europe
Ireland 	Europe
Netherlands 	Europe
Sweden 	        Europe
New Zealand 	Australia
Finland 	Europe
Belgium 	Europe
Russia 	        Europe
Taiwan	        Asia
Norway 	        Europe

Country	        # of universities
United Kingdom 	19
United States 	31
Switzerland 	3
Canada 	        4
Australia 	7
Hong Kong 	3
Japan 	        5
Singapore 	2
France 	        2
Denmark 	2
China 	        2
Korea, South 	2
Germany 	5
Ireland 	1
Netherlands 	4
Sweden 	        2
New Zealand 	1
Finland 	1
Belgium 	1
Russia 	        1
Taiwan	        1
Norway 	        1

# of universities
Africa	        ?
Asia	        ?
Antarctica      ?
Australia       ?
Europe	        ?
North America	?
South America	?

[/pre]

 

[bobhc]

Edited with back ticks

 

[nazmul_muneer]

Dear Pirduh,

Your data structure is not clear. Please upload a sample excel file


Regards,


Nazmul Muneer

 

[sdsurzh]

Hi pirduh,


Please use SUMIFS Formula you will get the following output.


Africa 0

Asia 15

Antarctica 0

Australia 8

Europe 42

North America 35

South America 0


Thanks,

SK

 

[srinidhi]

Pivot Table will be a good deal.

 

[bobhc]

Good day pirduh


If you use a Pivot Table as suggested by srinidhi you will easly achive what you are after.