Exceljet

Quick, clean, and to the point

Must pass 4 out of 6 subjects

Excel formula: Must pass 4 out of 6 subjects
Generic formula 
=IF(COUNTIF(range,">=70")>=4,"Pass","Fail")
Explanation 

To return "Pass" when any 4 subjects have a passing score, and "Fail" when not, you can use a formula based on the IF and COUNTIF functions. In the example shown, the formula in I5 is:

=IF(COUNTIF(C5:H5,">=70")>=4,"Pass","Fail")

where 70 represents the passing score for all subjects.

How this formula works

Working fron the inside out, this formula uses the COUNTIF function to count passing scores in all six subjects:

COUNTIF(C5:H5,">=70") // count passing subjects

The result in I5 is 3, in I6 is 5, and in I7 is 6. It's a good idea to use COUNTIF alone as you start this formula, to make sure you are getting the results you expect before adding more logic.

The number returned by COUNTIF is then checked against 4 with the greater than or equal to operator (>=), and the expression returns TRUE or FALSE for the logical test inside the IF function.

If 4 or more subjects have a passing score of at least 70, IF returns "Pass". If not, the IF function returns "Fail".

Must pass Math and English

If Math and English must have passing scores, no matter what other scores are present, the formula can be extended like this:

=IF(AND(COUNTIF(C5:H5,">=70")>=4,C5>=70,F5>=70),"Pass","Fail")

Here the AND function is used for the logical test inside IF:

AND(COUNTIF(C5:H5,">=70")>=4,C5>=70,F5>=70)

AND will return TRUE only when all three conditions are met:

  1. Passing score in 4 out of 6 subjects
  2. Passing score in Math
  3. Passing score in English

If you need "either/or" logic, you can use the OR function, or even combine OR with AND.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.