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