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.