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. 

wr2013_0-1710614124574.png

 

8 Replies

@wr2013 

One of several solutions.

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

@wr2013 

A solution for the future:

=LET(
    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.

@wr2013 

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)`;

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

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