Appending a total to the end of a UNIQUE list

[TonyD1016]

Following up from this thread, I am wondering if it is possible to take this formula to create essentially a dynamically updating table as new counties appear.

=LET(u,UNIQUE(SORT(County)),CHOOSE({1,2},u,COUNTIFS(County,u)))

This searches an existing list for time time a particular county appears then displays the results as:

County1 AMT
County2 AMT
County3 AMT

...and so on.

What I would like to do is append the word "Total" to the bottom of the list of counties and in the column next to it use a =SUM(COUNTIFS(County,UNIQUE(County))) to display the total number of times all counties have appeared.
So I would end up with :
County1 AMT
County2 AMT
County3 AMT
Totals SUM

 

[Fluff]

Personally I would stick to having the two formulae you had before & put them in row 2 (say D2& E2) then put the word Total in D1 & in E1

=SUM(E2#)

 

[eastrand]

Here's how I do something similar. Maybe you can adapt it.

LAMBDA append range

Excel does not provide a function to append ranges, but you can use the LAMBDA function to create a custom function to combine ranges. In the example below, the formula in cell C5 is: =AppendRange(E5:F9,H5:I10,"null") This formula combines the two ranges provided (E5:F9 and H5:I10), by appending...

exceljet.net

 

[Fluff]

However if you want it as part of the spill range try

=LET(u,UNIQUE(SORT(county)),c,COUNTIFS(county,u),s,SEQUENCE(ROWS(u)+1),IFERROR(INDEX(CHOOSE({1,2},u,c),s,{1,2}),CHOOSE({1,2},"TOTAL",SUM(c))))

 

[Eric W]

Kind of an odd formulation, but this also works:

=IFERROR(SORT(UNIQUE(CHOOSE({1,2},County,COUNTIF(County,IFERROR(INDEX(County,SEQUENCE(ROWS(County)+1)),"*"))))),"Total")