how count all result of UNIQUE function in another column of array?

[emq_excell]

hi

how count all result of UNIQUE function in another column of array?
Do it only with one formula and not with another column.
Thanks for all the help

[TMS]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"#"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[emq_excell]

Thank you
But I know this method
I want with a formula

[TMS]

With 365:

=LET(a,A2:A100,f,FILTER(a,a<>""),u,UNIQUE(f),c,COUNTIF(a,u),HSTACK(u,c))

[emq_excell]

MS-Off Ver : 2021

[TMS]

With 2021:

=LET(a,A2:A100,f,FILTER(a,a<>""),u,UNIQUE(f),c,COUNTIF(a,u),CHOOSE({1,2},u,c))

[alansidman]

if you have access to O365 Beta version then you could use this

=GROUPBY(Table1['#],Table1['#],COUNTA)

Otherwise, you will need to wait for someone else who can offer up a different formula. Good Luck. This is the best I can offer.

[TMS]

Thanks for the rep.