where 70 represents the passing score for all 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: If you need “either/or” logic, you can use the OR function, or even combine OR with AND.

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.