Excel : Automate COUNTIF (based on another table)

Question

I have a table that looks like this:

name nation
aaa  ESP
bbb  FRA
ccc  ESP
ddd  BEL
eee  FRA
fff  ITA

I have another table with :

country count
ESP     -
BEL     -
FRA     -
ITA     -

I would like my "count" column of the second table to count how many instances I have of that nation in the first table.

So that I get :

country count
ESP     2
BEL     1
FRA     2
ITA     1

I could enter in each cell of the "count" column:

COUNTIF('my 1st table nation column',"name of the targeted country")

The thing is I have 150 countries and I can't possibly edit all the country codes in each and every cell of the "count" column.

I also tried this workaround:

I selected the first cell of "count" and then highlighted the whole column. Then I typed:

COUNTIF('my 1st table nation column',"name of the country of the first country cell")

This way, I expected each cell of the "count" column to refer to the name of the "country" of its table row. But it didn't work.

Is there any other way (with a formula, not VB) I can automate the process so that each "count" cell reads its "country" and swipes through the "nation" column and count how many instances there are ?

Thank you.

Answer 1

=COUNTIF($B$2:$B$7,"="&D2)

name	nation		country	count
aaa	ESP		ESP	2
bbb	FRA		BEL	1
ccc	ESP		FRA	2
ddd	BEL		ITA	1
eee	FRA
fff	ITA