Explanation

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

Explanation

In this example, the goal is to calculate a weighted average of scores for each name in the table using the weights that appear in the named range weights (I5:K5) and the scores in columns C through E. A weighted average (also called a weighted mean ) is an average where some values are more important than others. In other words, some values have more “weight”. We can calculate a weighted average by multiplying the values to average by their corresponding weights, then dividing the sum of results by the sum of weights. In Excel, this can be represented with the generic formula below, where weights and values are cell ranges:

=SUMPRODUCT(weights,values)/SUM(weights)

The core of this formula is the SUMPRODUCT function. In a nutshell, SUMPRODUCT multiplies ranges or arrays together and returns the sum of products. This sounds really boring, but SUMPRODUCT is an incredibly versatile function that shows up in all kinds of useful formulas. See this page for an overview.

Worksheet example

In the worksheet shown, scores for 3 tests appear in columns C through E, and weights appear in the named range weights (I5:K5). The formula in cell G5 is:

=SUMPRODUCT(weights,C5:E5)/SUM(weights)

Looking first at the left side, we use the SUMPRODUCT function to multiply weights by corresponding scores and sum the result:

=SUMPRODUCT(weights,C5:E5) // returns  88.25

SUMPRODUCT multiplies corresponding elements of the two arrays together, then returns the sum of the product. We can visualize this operation in cell G5 like this:

=SUMPRODUCT({0.25,0.25,0.5},{90,83,90})
=SUMPRODUCT({22.5,20.75,45})
=88.25

The result is then divided by the sum of the weights:

=88.25/SUM(weights)
=88.25/SUM({0.25,0.25,0.5})
=88.25/1
=88.25

Note: when calculating a weighted average, it is common to assign weights that add up to the number 1. As you can see above, when the weights do add up to 1, the denominator becomes 1 and has no effect in the formula. However, it is not required that weights add up to 1, and the general form of the formula used above is meant to handle either case. See below for an example where weights do not add up to 1.

As the formula is copied down column G, the named range weights I5:K5 does not change, since it behaves like an absolute reference . However, the scores in C5:E5, which are relative reference , change with each new row. The result is a weighted average for each name in the list as shown. For easy reference, the average in column F is calculated normally with the AVERAGE function :

=AVERAGE(C5:E5)

Weights that do not sum to 1

In the example above, the weights are configured to add up to 1, so the divisor is 1, and the final result is the value returned by SUMPRODUCT. However, a nice feature of the formula is that the weights don’t need to add up to 1. For example, we could use a weight of 1 for the first two tests and a weight of 2 for the final (since the final is twice as important) and the weighted average will be the same:

Excel weighted average with custom weights - 1

In cell G5, the formula is solved like this:

=SUMPRODUCT(weights,C5:E5)/SUM(weights)
=SUMPRODUCT({1,1,2},{90,83,90})/SUM(1,1,2)
=SUMPRODUCT({90,83,180})/SUM(1,1,2)
=353/4
=88.25

Note: the values in curly braces {} above are arrays , which map directly to ranges in Excel.

Transposing weights

The SUMPRODUCT function requires that array dimensions be compatible to run correctly. For example, if the data is in a horizontal array , the weights should also be in a horizontal array. If dimensions are not compatible, SUMPRODUCT will return a #VALUE error. To prevent this problem, you may need to transpose the weights to match the data. In the example below, the weights are the same as the original example above, but they are listed in a vertical range:

Excel weighted average with TRANSPOSE - 2

In this case, to calculate a weighted average with the same formula, we need to “flip” the weights into a horizontal array with the TRANSPOSE function like this:

=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)

After TRANSPOSE runs, the vertical array:

=TRANSPOSE({0.25;0.25;0.5}) // vertical array

becomes a horizontal array like this:

={0.25,0.25,0.5} // horizontal array

Note the semicolons are now commas, which indicate a horizontal array. From this point, the formula is solved the same way as explained earlier.