Excel Count /Unique Dynamic Array

Question

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

You could try something like =LET(X,UNIQUE(B2:B5),CHOOSE({1,2},X,BYROW(X,LAMBDA(A,COUNT(UNIQUE(FILTER(A2:A5,B2:B5=A))))))). Very similar to @Scottcraner's answer. Otherwise, just load the data into an pivot-table. It has a function to count unique values just fine. No need for formulae. Make sure to tick "Add this data to the Data Model". — JvdV
– JvdV, Commented Mar 21, 2022 at 22:06

Scott Craner · Accepted Answer · 2022-03-21 20:34:29Z

2

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

edited Mar 21, 2022 at 20:34

answered Mar 21, 2022 at 19:34

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scott Craner Over a year ago

@Prakriti please edit your original post to include any clarifications, please do not try to put clarifications in comments.

Prakriti Over a year ago

=COUNT(UNIQUE(FILTER($A$2:$A$5,D2=$B$2:$B$5))) - Tried already - Does not work . I am trying to group this result into the format given above ! Following Line of Code works : =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)) Thankyou for all your help

Mayukh Bhattacharya · Accepted Answer · 2022-03-21 21:36:26Z

2

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

edited Mar 21, 2022 at 21:36

answered Mar 21, 2022 at 20:53

Mayukh Bhattacharya

29.2k9 gold badges31 silver badges46 bronze badges

4 Comments

Prakriti Over a year ago

=COUNT(UNIQUE(FILTER($A$2:$A$5,D2=$B$2:$B$5))) - Tried already - Does not work . I am trying to group this result into the format given above :

Mayukh Bhattacharya Over a year ago

But the image clearly shows it works, however you should try the solution provided by @ScottCraner Sir

Mayukh Bhattacharya Over a year ago

@Prakriti please show us a screenshot where it is not working for you

Prakriti Over a year ago

=COUNT(UNIQUE(FILTER($A$2:$A$5,D2=$B$2:$B$5))) - Tried already - Does not work . I am trying to group this result into the format given above ! Following Line of Code works : =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)) Thankyou for all your help

anderson · Accepted Answer · 2022-03-21 18:54:28Z

1

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

answered Mar 21, 2022 at 18:54

anderson

312 bronze badges

4 Comments

Prakriti Over a year ago

Thankyou ! =COUNTA(UNIQUE(A2:A5&B2:B5))-will add all the columns . My expection for result :

Prakriti Over a year ago

Thankyou ! yes Colume -A - Date , Column -B - Name =COUNTA(UNIQUE(A2:A5&B2:B5))-will add all the columns for A, B . My expection for result is A = 1 and B =2 coz refering first example as no of date entered result on Column B - should be change .

anderson Over a year ago

So you need for each name how many entries are different in date?

Prakriti Over a year ago

Yes I was able to execute result with Lambda line of Code. My goal on this task is to executed multiple entries with Multiple date and expected output count. I did not get any results with following code =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))

Collectives™ on Stack Overflow

Excel Count /Unique Dynamic Array

3 Answers 3

2 Comments

UNIQUE DISTINCT COUNT

4 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

UNIQUE DISTINCT COUNT

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related