Must pass 4 out of 6 subjects

In this example, the goal is to create a formula that will return "Pass" or "Fail" depending on whether a student has a passing score in at least 4 out of 6 subjects. This problem can be easily solved with a formula based on the COUNTIF function together with the IF function in a formula like this:

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

COUNTIF function

The COUNTIF function counts cells in a range that meet a single condition, referred to as criteria. COUNTIF supports logical operators (>,<,<>,<=,>=) and wildcards (*,?) for partial matching. The generic syntax for COUNTIF looks like this:

=COUNTIF(range,criteria)

In this case, we need to configure COUNTIF to count passing scores in columns C:H for each name listed in column B. We start off with the range:

=COUNTIF(C5:H5,

For criteria, we need to use the greater than or equal to operator (>=) with the passing score of 70:

=COUNTIF(C5:H5,">=70")

Notice the criteria is entered in double quotes (""), which is a quirk of RACON functions in Excel. The formula above is in fact the formula entered in cell J5, copied down. As you can see in the worksheet shown above, COUNTIF returns a count of passing scores in each row. If desired, column J could be used as a helper column, but in this example the formula in column K is an all-in-one formula and the formula in column J is just for reference.

IF function

To return "Pass" or "Fail", we use the IF function. We start off by nesting COUNTIF inside IF like this:

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

We know from above that the COUNTIF formula returns a count of passing scores. Because the goal in the example is to return "Pass" when there are at least 4 subjects with passing scores, we add logic to test the result from COUNTIF:

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

This is the logical_test inside the IF function. If COUNTIF returns the number 4 or greater, the logical test will return TRUE. Otherwise, the logical test will return FALSE. Finally, we need to add the value_if_true and value_if_false arguments and close up the formula. The final formula in K5 is:

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

As the formula is copied down, if 4 or more subjects have a passing score of at least 70, IF returns "Pass". Otherwise, the IF function returns "Fail".

Must pass Math and English

If Math and English must have passing scores, regardless of other scores, the formula can be adjusted 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)

With this configuration, AND will return TRUE only when these three conditions are TRUE:

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

If any condition is not true, AND will return FALSE and the IF function will return "Fail" as a final result