Excel Formulas - Compare cells to meet multiple criteria within a row

Count rows in a table when they meet multiple criteria within a row

Syntax of used function(s)

SUMPRODUCT(array1, [array2], [array3], ...)

The SUMPRODUCT function is used to multiplies the corresponding components in the given arrays, and returns the sum of those products.

What to do?

The given table contains the name, class, score of 1st semister and 2nd semister of some students. For a particular class we want to count the matches rows where the performence of the concerned student is improved. It is easy to count the rows where the specific class exists.
Therefore within those rows where the score of 2nd semister is greater than the 1st semister.

The uses of SUMPRODUCT function is a good way to solve this problem, because the SUMPRODUCT function can handled the array operations.

Formula

=SUMPRODUCT((C6:C11=G8)*(D6:D11<E6:E11))

How this formula works

The default behaviour of SUMPRODUCT function is to multiply corresponding elements in one or more arrays together, then sum the products. In case of given a single array, it returns the sum of the elements in the array.
In the above example, two logical expression have used inside a single argument. After evaluation of two logical expression the formula looks like:

=SUMPRODUCT(({FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;})*({TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;}))

The multiplication operator have been used to multiply the two arrays together and excel will automatically convert the logical value to ones and zeros.
After multiplication, the formula looks like -

=SUMPRODUCT({0;0;0;1;0;1;})

And finally with only one array, SUMPRODUCT simply adds up the elements in the array and returns the sum.

Previous: Excel Formulas - Compare columns of a row
Next: Excel Formulas - Count number of items in the list