# 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.

﻿