Must pass 4 out of 6 subjects
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:
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:
Here the AND function is used for the logical test inside IF:
AND will return TRUE only when all three conditions are met:
- Passing score in 4 out of 6 subjects
- Passing score in Math
- Passing score in English
If you need "either/or" logic, you can use the OR function, or even combine OR with AND.