Listing top three names which have the most blank values-CodePudding

I have two classes (ABC and XYZ) and some students which have taken test in these classes. However, due to personal reason, a few students couldn't take several of the exams. I want to find the top 3 amounts of test not taken and top 3 names of the students that haven't taken the most exams. Below is an illustration of what I'm trying to do:

In cell F3, I've written the following code to get the top 3 amount of test not taken:

=LARGE(COUNTIFS(C:C,"="&"",B:B,UNIQUE(FILTER(OFFSET($B$2,0,0,COUNTA(B:B)-1,1),OFFSET($A$2,0,0,COUNTA(A:A)-1,1)=$F$2))),ROWS(B$2:B2))

My goal is to now list the top 3 non test taker names. I've tried a variation of the above code but can't seem to make it work. I have excel version 2209 if this helps. Thank you in advanced!

CodePudding user response：

Try:

Formula in E2:

=LET(x,UNIQUE(FILTER(B:B,(A:A=F1)*(C:C=""))),SORTBY(x,MAP(x,LAMBDA(y,COUNTIFS(A:A,F1,B:B,y,C:C,""))),-1))

Or, for both names and count:

=LET(x,UNIQUE(FILTER(B:B,(A:A=F1)*(C:C=""))),SORT(HSTACK(x,MAP(x,LAMBDA(y,COUNTIFS(A:A,F1,B:B,y,C:C,"")))),2,-1))