# Counting the number of occurrences of an entity in a table

Copper Contributor

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

8 Replies

# Re: Counting the number of occurrences of an entity in a table

One of several solutions.

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

# Re: Counting the number of occurrences of an entity in a table

A solution for the future:

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

# Re: Counting the number of occurrences of an entity in a table

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.

# Re: Counting the number of occurrences of an entity in a table

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.

# Re: Counting the number of occurrences of an entity in a table

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

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

# Re: Counting the number of occurrences of an entity in a table

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

# Re: Counting the number of occurrences of an entity in a table

select * from splitCell;

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

# Re: Counting the number of occurrences of an entity in a table

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