Google Sheets/Excel Formula Countifs Distinct Lead IDs by Name

Question

I have a dataset which has various names which had tied associated Lead IDs. In this dataset there are records the name will be matched with the same Lead ID multiple times for example:

Does anyone have suggestions how a single excel formula could return results such as this for Google Sheets/Excel?

Answer 1

Within sheets you may try:

=map(unique(tocol(A2:A,1)),lambda(Σ,{Σ,countuniqueifs(B:B,A:A,Σ)}))

Answer 2

Not using LAMBDA Helper functions and using COUNTIFS:

=LET(A,A2:A10, B,B2:B10, uxA,UNIQUE(A), uxB,UNIQUE(B),
 VSTACK({"Name","Total Distinct Lead ID"}, 
  HSTACK(uxA,MMULT(N(COUNTIFS(A,uxA,B,TOROW(uxB))>0),SEQUENCE(ROWS(uxB),,,0)))))

Here is the output:

COUNTIFS counts per unique values (uxA) of name A the unique values (uxB) of name B, i.e. on cell i,j returns the total counts for Lead ID of uxB at column j for uxA at row i. Here the output of COUNTIFS for the input data:

2   1   0   0   0   0
0   0   1   0   0   0
0   0   0   2   2   1 

Since we are interested only in distinct values, we use N() function to replace the count values with 1 where it is greater than 0, otherwise return 0. In order to have the total distinct count we use MMULT to sum the total number of ones on each row. SEQUENCE builds the row array of 1's to do the multiplication. Finally, we use VSTACK to generate the header and HSTACK to put the output in the desired format.

If you prefer to use a Lambda Helper function instead of MMULT, you can try the following:

=LET(A,A2:A10, B,B2:B10, uxA,UNIQUE(A), uxB,UNIQUE(B),
 VSTACK({"Name","Total Distinct Lead ID"},
  HSTACK(uxA,BYROW(COUNTIFS(A,uxA,B,TOROW(uxB)),LAMBDA(x,SUM(N(x>0)))))))

or just the following taking a different approach:

=LET(A,A2:A10, B,B2:B10, uxA,UNIQUE(A),
 VSTACK({"Name","Total Distinct Lead ID"},
   HSTACK(uxA,BYROW(uxA,LAMBDA(x,ROWS(UNIQUE(FILTER(B,A=x))))))))