How can I make an adaptive list to check against another adaptive list?

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:

And Reviewers should return:

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.

Power Pivot & Measure:

Just for the sakes of alternatives I added a Power Pivot Table option:

Steps I took:

• Select A1:B9;

• In the 'Power Pivot' tab add the data to the Data Model;

• Created a new measure named '#Blank', refering to:

  =COUNTBLANK([Submission])
  

• Add a Power Pivot Table;

• Under the 'PivotTable Fields' add name to the 'Rows' area and the newly created measure to 'Values';

• Under the 'PivotTable Options' choose to show items when no data on rows;

• Under the 'PivotTable Options' choose to show a zero for empty cells.

Did I overcomplicate this? Possibly!

PowerQuery:

Another cool idea is to load the data into PowerQuery. Now you could transform the 'submission' column, and group this by 'name'.

Here is the M-Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Name"}, {{"#Blank", each List.Sum(List.Transform([Submission], (n)=> if n is null then 1 else 0)), type number}})
in
    Group

Or:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Name"}, {{"#Blank", each List.Accumulate([Submission],0,(s,c)=>if c is null then s+1 else s), type number}})
in
    Group

Or, even better:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Name"}, {{"#Blank", each List.Count(List.Select([Submission],(n)=> n is null)), type number}})
in
    Group

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