Forum Discussion

ELingo2275's avatar
ELingo2275
Copper Contributor
Sep 01, 2024

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.

 

 

Excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 01, 2024

    ELingo2275 

    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.

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Sep 02, 2024

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

     

     

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Sep 02, 2024

    ELingo2275 

    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)

Resources