COUNTIF from Dynamic Range based on Cell Input - Multiple Columns

Hello Expert's,

1) I need to find the student names who are in Top 25 based on Dynamic inputs(exam numbers) entered in Cell O7 & O8, P7 & P8, Q7 & Q8, R7 & R8.

2) Please refer enclosed Workbook is self explanatory & detailed requirement in "Sheet 1" worksheet.

Thanks in Advance!

In O11

=SUMPRODUCT(($B$11:$K$35=$N11)*($B$10:$K$10>=O$8)*($B$10:$K$10<=O$9))

Copy across and down

Top 25 based on what criteria ?

Please try for the whole table (inclusive Student names) in one go:

Formula:

Please Login or Register  to view this content.

If you replace the SORT parameter 2 nearly at the end of the formula by IFNA(MATCH(1,LEN(N10:R10),0),2) you can sort the table by placing 1 character in the column you want to sort by.

Another way to enable sorting is by using a data valadation list in S10, in which the user can select the column on which the table should be sorted.

In this case, the SORT parameter is MATCH(S10,N10:R10,0)

Given Hd's reply (selecting a column to SORT): IMHO it would be better to have a single column and select the exam ranges and sort on this column

Sorting on column N and (say Q) is confusing because columns O, P and R are "mis-represented" after the sort.

@JT, I built the SORT in a similar way that the OP could sort its original sheet of post #1 as well

Thanks for the feedback and rep . I'm glad to have helped.