Excel Formulas - Compare columns of a row

Count rows in a table when they compare two columns on a specific criteria

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.

Explanation

To compare between two columns of a row from a range of cells the SUMPRODUCT function can be used.

Formula

=SUMPRODUCT(--(C6:C11>D6:D11))

How this formula works

The SUMPRODUCT function accepts one or more array. In the above example, each cell of range C6:C11 compare to the cell of same row in the range D6:D11. From the above example, if the cell from the range C6:C11 is greater than the cell from the range D6:D11 of same row, returns TRUE or FALSE in an array, like -

{FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;}

The double negative (--) sign converts the FALSE or TRUE into 0 and 1 and the resulting array looks like -

{0;1;1;0;0;0;}

Finally the SUMPRODUCT then sums the items in this array and returns the total, in the example, it is the number 2.

Previous: Excel Formulas - Count number of cells begins with string
Next: Excel Formulas - Compare cells to meet multiple criteria within a row