Counting the number of occurrences of an entity in a table

[wr2013]

I am trying to automate the counting of the number of times a country appears in a table.  See below example.

             This table is original.                                                                   This is what I want. 

[#2]

@wr2013 

One of several solutions.

=SUM(--ISNUMBER(SEARCH(D2,$A$1:$A$6)))

[#3]

@wr2013 

A solution for the future:

=LET(
    all, TEXTSPLIT(TEXTJOIN(", ", , countries), , ", "),
    GROUPBY(all, all, COUNTA)
)

[#4]

@Detlef_Lewin 

I tried your formula and it just returned the number "1" for each line, but should have returned a "6" for the last two country lines.

[#5]

Thanks for your reply, but when I use your formula all I get is "#NAME?". I am probably entering the formula incorrectly or do not understand how to define the search area or where the results are to be listed. Sorry.

[#6]

@wr2013 

Try confirming @Detlef_Lewin's formula by pressing Ctrl+Shift+Enter.

The other one will only work in Excel in Microsoft 365.

[#7]

I still only get the number "1" in each row.

[#8]

select * from splitCell;
cli_split_data~splitCell~[;,]~ROAD NAMES (EDITOR);
select `ROAD NAMES (EDITOR)`,count(1) from splitCellsplit group by `ROAD NAMES (EDITOR)`;

https://e.anyoupin.cn/EData/?s=1165

[#9]

@wr2013 Try COUNTIF instead:

=COUNTIF($A$1:$A$6, "*"&D2&"*")

Enter the formula in cell E2, then drag/copy down to cell E9.

If you have Excel for MS365, only one formula is needed in cell E2 to spill the results for the entire criteria range:

=COUNTIF(A1:A6, "*"&D2:D9&"*")

Also, with Excel for MS365, you can generate the unique list of countries and spill the results in a single cell dynamic array formula as follows:

=LET(
    rng, A1:A6,
    arr, ", "&rng&", ",
    cols, MAX(LEN(arr)-LEN(SUBSTITUTE(arr, ",", )))-1,
    unq, SORT(UNIQUE(TOCOL(TEXTBEFORE(TEXTAFTER(arr, ", ", SEQUENCE(, cols)), ", "), 2))),
    HSTACK(unq, COUNTIF(rng, "*"&unq&"*"))
)

See attached...