Frequency distribution for unique values

Copper Contributor

Frequency distribution for unique values

I have a list of tree species and need to figure out the frequency distribution for each species. I know how to get a list of the unique tree species =SORT(UNIQUE(A1:A30)). However I'm unsure how to get a number next to the species (i.e. maple appeared 5 times, ash  17 times, etc). Any help is appreciated. Thank you!

The data initially looks like below.

4 Replies

Re: Frequency distribution for unique values

Let's say your formula =SORT(UNIQUE(A1:A30)) is in cell K1.

In L1:

=COUNTIF(A1:A30, K1#)

Alternatively, create a pivot table based on the data, and add the tree species column to both the Rows area and the Values area.

Re: Frequency distribution for unique values

=SORT(UNIQUE(A1:A30))

=COUNTIF(\$A\$1:\$A\$30, B1)

Re: Frequency distribution for unique values

@ELingo2275 GROUPBY() would be perfect function for you. Groupby is available to latest update release to Microsoft 365 only.

``=GROUPBY(A2:A30,A2:A30,COUNTA)``

You can also try the following formula.

``=HSTACK(UNIQUE(A2:A30),COUNTIFS(A2:A30,UNIQUE(A2:A30)))``

Re: Frequency distribution for unique values

The 'best' answer depends on where this study is going, what additional information relating to the trees, their maturity, their condition, their location/habitat, and what you wish to demonstrate.  For example, if you wanted a geographical distribution you might have formulas such as

``````= LAMBDA(species,habitat,LAMBDA(selected,
LET(
distinct, UNIQUE(FILTER(species, habitat=selected)),
count,    GROUPBY(distinct, distinct, COUNTA),
count
)
))(species, habitat)(selectedHabitat)``````