# Listing top three names which have the most blank values

Advertisements

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!

### >Solution :

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