# 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

**Weekly Trends and Language Statistics**- Weekly Trends and Language Statistics