Forum Discussion
Counting the number of occurrences of an entity in a table
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.
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.
Try confirming Detlef_Lewin's formula by pressing Ctrl+Shift+Enter.
The other one will only work in Excel in Microsoft 365.
A solution for the future:
=LET( all, TEXTSPLIT(TEXTJOIN(", ", , countries), , ", "), GROUPBY(all, all, COUNTA) )- 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.
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=1165wr2013 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...
