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