Excel Count /Unique Dynamic Array-CodePudding

I need to count unique values between two columns I have tried count(Unique())formula. Results entered all values instead of separate value with column

Input :

Date       Name 
1/1/2022    A
1/1/2022    A
1/1/2022    B 
1/2/2022    B

Output : Column D Column E A 1 B 2

CodePudding user response：

Use LET with BYROW and LAMBA in a FILTER:

=LET(dt,A2:A5,nme,B2:B5,unqnme,UNIQUE(nme),unqall,UNIQUE(CHOOSE({1,2},dt,nme)),cnt,BYROW(unqnme,LAMBDA(a,COUNT(FILTER(unqall,INDEX(unqall,0,2)=a)))),CHOOSE({1,2},unqnme,cnt))

CodePudding user response：

UNIQUE DISTINCT COUNT

Well, earlier the query was bit confusing, although it was clear that OP needed Unique Distinct Count, but wasn't sure whether needed those colons : in between and Name in beginning or not . However here is another way you may also try.

• Formula used in cell D2

=UNIQUE(B2:B5)

• Formula used in cell E2

=COUNTA(UNIQUE(FILTER($A$2:$A$5,D2=$B$2:$B$5)))

Alternative formula to get unique distinct counts

• Formula used in cell F2

=SUM(--(FREQUENCY(IF($B$2:$B$5=D2,MATCH(A$2:A$5,A$2:A$5,0)),ROW(B$2:B$5)-ROW(B$2) 1)>0))

CodePudding user response：

If i understand your question, maybe this can help you: =COUNTA(UNIQUE(A2:A5&B2:B5)) A column is date and B column is Name