Unique count with criteria in Excel

Excel question:

I have 2 tabs:

Tab 1, primary data:

Taba 2, editing this taab

In tab 2,
I want to show unique count of addresses for 1 group (column3).
My result should automatically update column 2 and 3 when I input Row 1 unique Id. I'm using xlookup to update column 2 from tab 1. How do I get column 3 unique count?

Is this possible?

I tried https://www.statology.org/excel-count-unique-by-group/ and the formula kept breaking when something was added. Want a formula that updates if new rows are added.

Unique Count With Criteria

=LET(di,A2:A10,si,'Tab1'!A2:A10,sg,'Tab1'!B2:B10,sa,'Tab1'!C2:C10,
    dg,XLOOKUP(di,si,sg),dgu,UNIQUE(dg),
    uc,BYROW(dgu,LAMBDA(r,ROWS(UNIQUE(FILTER(sa,sg=r))))),
    gm,INDEX(uc,XMATCH(dg,dgu)),
HSTACK(dg,gm))

• You could add some error handling by using the 4th parameter of XLOOKUP for the Group column and by using IFERROR for the Count column (not used in the screenshot).

=LET(di,A2:A11,si,'Tab1'!A2:A10,sg,'Tab1'!B2:B10,sa,'Tab1'!C2:C10,
    dg,XLOOKUP(di,si,sg,""),dgu,UNIQUE(dg),
    uc,BYROW(dgu,LAMBDA(r,ROWS(UNIQUE(FILTER(sa,sg=r))))),
    gm,IFERROR(INDEX(uc,XMATCH(dg,dgu)),""),
HSTACK(dg,gm))

• Note that you can get rid of the gm variable. Its purpose is just to make it a bit more readable.

Assuming no Excel version constraints as per the tags listed in the question:

=LET(in, A2:C7, B, INDEX(in,,2), C, TAKE(in,,-1),
 HSTACK(TAKE(in,,2), MAP(B, LAMBDA(x, ROWS(UNIQUE(FILTER(C,B=x,0)))))))

Another alternative:

=LET(in, A2:C7, B,INDEX(in,,2), C,TAKE(in,,-1), uxC,UNIQUE(C),
 cnts, MMULT(N(COUNTIFS(B, B, C,TOROW(uxC))>=1), SEQUENCE(ROWS(uxC))^0),
 HSTACK(TAKE(in,,2), cnts))

Here is the output:

If you are able to format both sets of data as tables then in the second table you can do

column 1 formula:

    =Table1[@UniqueID]

column 2 formula:

    =Table1[@Group Name]

column 3 formula

    =COUNTIFS(Table1[Address],Table1[@Address],Table1[Group Name],[@[Group Name]])