Count names and # routes using the unique function

Hello

I was wondering if there is an all in one function/formula in excel 365, to output (in the summary table) the names and count the number of routes completed. Please see attached sample.

Thank you

If you change your 365 update channel to the beta channel, you can try this:

=VSTACK(HSTACK("Name","Total # Rte"),GROUPBY(B2:B15,A2:A15,COUNT))

If you don't want to do that, try this instead:

=LET(u,UNIQUE(B2:B15),c,COUNTIF(B2:B15,u),VSTACK(HSTACK("Name","Total # Rte"),SORT(HSTACK(u,c),1),HSTACK("Total",SUM(c))))

1. There's a stray space after Mike in B13 - "Mike ".

2. Delete ALL expected results.

3. Use:

=LET(A,B2:B15,B,UNIQUE(A),C,BYROW(B,LAMBDA(x,COUNTIF(A,x))),VSTACK({"Name","Total # routes"},HSTACK(B,C),HSTACK("Total",ROWS(A))))

4. See file.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.