Excel formula

Question

Good day,

I am using a formula to determine the best 5 marks in a range, while adding them to another mark as follows:

{=SUMPRODUCT(AGGREGATE(15,6,IF(K10:K22>0,K10:K22),{1,2,3,4,5}))+K9}

The marks are 1 - 9 with one being the best and 9 the worst. I want to put a condition that would allow if there are less than 6 marks with 8 or better from that formula for the cell to write text like "FAIL".

Answer 1

=IF(COUNTIF(K10:K22,"<=8")<6,"FAIL",SUMPRODUCT(AGGREGATE(15,6,IF(K10:K22>0,K10:K22),{1,2,3,4,5}))+K9)

The { } indicate that it is an array formula. To save a formula as an array formula, confirm it with Ctrl+Shift+Enter.

Answer 2

The original formula worked but on another sheet, I am facing this challenge:

=IF(COUNTIF(G12:G20,"˃=39")<6,"FAIL",SUM(LARGE(G12:G20,{1,2,3,4,5}))+G11)

I tried your solution as stated above but I am not getting the same result.

Here I am trying to see if the mark is less than or equal to 39 in one of the best six in a range, it should indicate FAIL.

Where am I going wrong and what is the correct formula?

Answer 3

THANK YOU VERY MUCH!

You are a true MVP and you have made my day!