Identify unique value, lookup in another column, then count distinct values in adjacent column.

[MarkS1234]

Please see example below. ID and Animal will be entered in to worksheet. I have entered a formula to identify the unique IDs in Column A. But I then need to calculate how many different animals for each ID. The desired values are in red.

 

[HighAndWilder]

Please see example below. ID and Animal will be entered in to worksheet. I have entered a formula to identify the unique IDs in Column A. But I then need to calculate how many different animals for each ID. The desired values are in red.

=LET(d,CHOOSECOLS(UNIQUE(A2:B12),1),GROUPBY(d,d,COUNTA,0,0))

 

[MarkS1234]

Thank you so much. Trying it now.

 

[MarkS1234]

Works a treat! Thank you, I could never writen that myself.