I apologize from the start as I am not allowed to share the workbook I am working on (it has confidential information from work) but I will do my best to explain what is happening and what my issue is.
I have two sheets, "Tracker" and "Reviewers". In the tracker names are recorded in column L and their submission is recorded in column M. Everything runs on a serial code in column A so there are blank cells between names. Some people have multiple submissions so their names show multiple times in column L. In the reviewers sheet, I have:
=UNIQUE(FILTER(Tracker!L4:L4999,Tracker!L4:L4999<>0))

In cell A2 to pull all the names of people who have a submission. This works flawlessly and adapts to include any new people. Then in cell B2 I have written:
=SUMPRODUCT(IF(ISBLANK(FILTER(Tracker!$L$4:$M$4999,Tracker!$L$4:$L$4999=Reviewers!A2#))=TRUE,1,0))

The idea here was to get a count of how many "submissions" people have without actually writing anything. It is filtering the list of names and submissions by name in the list we just created, checking if their "submission" is a blank cell, then adding them up. Issue is that it works when I filter by cell A2 but not when I filter by the function that spills out of cell A2 (A2#). I need it to be adaptive so if new names are added it can make the list longer, hence why I cannot just pull the cells down the list (A2, A3, A4,...). How would you go about getting a check of how many are blank like this?
As an example, Tracker could have:




Name
Submission




Jim
Idea


Bob
Idea


Pam



Sam
Idea


Jim



Bob
Idea


Jim



Pam
Idea




And Reviewers should return:




Name
#Blank




Jim
2


Bob
0


Pam
1


Sam
0




I hope this makes sense and I hope you can help me edit the equation in cell B2 of the Reviewers sheet to be adaptive and spill the results.

How can I make an adaptive list to check against another adaptive list?
=LET(d,DROP(FILTER(A:B,A:A<>""),1),
     n,INDEX(d,,1),
     s,INDEX(d,,2),
     u,UNIQUE(n),
     m,MMULT(--(TOROW(n)=u),--(s="")),
HSTACK(u,m))

Change the filter range (and maybe the lines to drop) and the index numbers to your situation.

I think this would work in your case:
=LET(d,DROP(FILTER(Tracker!$L$4:$M$4999,Tracker!$M$4:$M$4999<>""),1),
     n,INDEX(d,,1),
     s,INDEX(d,,2),
     u,UNIQUE(n),
     m,MMULT(--(TOROW(n)=u),--(s="")),
HSTACK(u,m))

