Forum Discussion

wr2013's avatar
wr2013
Copper Contributor
Mar 16, 2024

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.                                                    ...
excel
Formulas and Functions
djclements's avatar
djclements
Bronze Contributor
Mar 16, 2024

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

Resources