Return a list of names based on multiple criteria

  • Re: Return a list of names based on multiple criteria


    Assuming same workbook.. add another column to return the Test scores in descending order.


    So now, for Quiz 1 you will have 3 columns.. Name, Progress, Test Score.


    These formulas are to get Male Only.


    In C2:


    [COLOR="#0000FF"]=IF(D2="","",INDEX(Sheet1!$A$2:$A$19,SMALL(IF(Sheet1!$C$2:$C$19=$B$1,IF(Sheet1!$B$2:$B$19="M",IF(Sheet1!$D$2:$D$19=D2,IF(Sheet1!$E$2:$E$19=E2,ROW(Sheet1!$D$2:$D$19)-ROW(Sheet1!$D$2)+1)))),COUNTIFS(D$2:D2,D2,E$2:E2,E2))))[/COLOR]


    CSE Confirmed and copied down.


    In D2:


    [COLOR="#0000FF"]=IFERROR(LARGE(IF(Sheet1!$C$2:$C$19=$B$1,IF(Sheet1!$B$2:$B$19="M",Sheet1!$D$2:$D$19)),ROWS(C$2:C2)),"")[/COLOR]


    confirmed with CTRL+SHIFT+ENTER and copied down


    in E2:


    [COLOR="#0000FF"]=IFERROR(LARGE(IF(Sheet1!$C$2:$C$19=$B$1,IF(Sheet1!$B$2:$B$19="M",IF(Sheet1!$D$2:$D$19=D2,Sheet1!$E$2:$E$19))),COUNTIF(D$2:D2,D2)),"")[/COLOR]


    CSE Confirmed and copied down.


    I will leave the rest to you....

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Return a list of names based on multiple criteria


    I have managed to figure out the formulas for the female only but am struggling to get the ones for combined genders. It's been an hour but no luck. Can you help?

  • Re: Return a list of names based on multiple criteria


    For both genders, you will need to take out the IF(Sheet1!$B$2:$B$19="M", part from the formulas (you will need to remove one (1) closing parenthesis before the ROWS(C$2:C2) bit or the COUNTIF(S) bit also.


    For C2:


    [COLOR="#0000FF"]=IF(D2="","",INDEX(Sheet1!$A$2:$A$19,SMALL(IF(Sheet1!$C$2:$C$19=$B$1,IF(Sheet1!$D$2:$D$19=D2,IF(Sheet1!$E$2:$E$19=E2,ROW(Sheet1!$D$2:$D$19)-ROW(Sheet1!$D$2)+1))),COUNTIFS(D$2:D2,D2,E$2:E2,E2))))[/COLOR]


    D2:


    [COLOR="#0000FF"]=IFERROR(LARGE(IF(Sheet1!$C$2:$C$19=$B$1,Sheet1!$D$2:$D$19),ROWS(C$2:C2)),"")[/COLOR]


    E2:


    [COLOR="#0000FF"]=IFERROR(LARGE(IF(Sheet1!$C$2:$C$19=$B$1,IF(Sheet1!$D$2:$D$19=D2,Sheet1!$E$2:$E$19)),COUNTIF(D$2:D2,D2)),"")[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!