Counting the number of occurrences of an entity in a table

Copper Contributor

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. 



8 Replies


One of several solutions.



A solution for the future:

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

@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.

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.


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

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

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

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

@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:


    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...