Unique count with criteria in Excel

Question

Excel question:

I have 2 tabs:

Tab 1, primary data: Tab 1, primary data

Taba 2, editing this taab tab 2, editing this tab

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.

Answer 1

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))

enter image description here

• 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.

Answer 2

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: output

• VBasic2008: The expected result in the last column is all 1s corresponding to your screenshot: the unique count per group (Name).
• David Leal: @VBasic2008 thanks, I see, so the count is based on the Address column. Make sense, I need to review it then.

Answer 3

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]])