# Excel Formulas - Count number of matches between two columns

## Count number of matches between two columns

**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 two columns within two different ranges and count if the value of range1 found in range2, the SUMPRODUCT function can be used.

**Formula**

```
=SUMPRODUCT(--(B6:B12=D6:D12))
```

**How the formula works**

In the above example the value in the range B6:B12 compare with the value in the range D6:D12 and generates an array with TRUE and FALSE, and the formula looks like -

```
=SUMPRODUCT(--({TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;}))
```

The '--' signs convert the TRUE and FALSE into 1 and 0, and the formula after that looks like -

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

There is no other array to multiply, so the SUMPRODUCT function simply sums the value in the array and return results.

**Previous: ** Excel Formulas - Count number of items in the list

**Next:**
Excel Formulas - Count numbers beginning with a specific number

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