Summary

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.

Generic formula

=IF(COUNTIF(range,">=70")>=4,"Pass","Fail")

Explanation 

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.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.