I have a list of student who need to score minimum 40 marks for each subject(five subjects) to get position. For example if a student does not get more than or equal to 40 marks in each subject, he/she will not be ranked and should be skipped from giving position. The following is my current not working solution.

=IF(IF(COUNTIF(C2:G2,">=40")=5,TRUE,FALSE), RANK(H2,H$2:H$6),"NA")

This does not work correct because, it does not skip the ranking. Please help.

2 Answers 2


Instead of COUNTIF(C2:G2,">=40")=5 I would use the following, for simplicity:

IF(min(C2:G2)<40, "FAIL", "PASS")

i.e. if the smallest number is less than 40, FAIL, but if they're all above 40 so the smallest of all the numbers is more than/equal to 40, PASS. In your example you'll replace "PASS" with the rank formula


You don't need two IF() statements if there are only two possible choices. A good explanation of this was given recently.

Quoting that answer, the IF() statement is simply:

=IF(Condition, What to do if true, What to do if false).

You have the condition correct: COUNTIF(C2:G2,">=40")=5
... and the what to do if true: RANK(H2,H$2:H$6)
... and the what to do if false: "NA"

So all you need to do is combine them in one IF() statement.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .