If you want to compare two columns and count matches in corresponding rows, you can use the SUMPRODUCT function with a simple comparison of the two ranges. For example, if you have values in B5:B11 and C5:C11 and you want to count any differences, you can use this formula:
How this formula works
The SUMPRODUCT function is a versatile function that handles arrays natively without any special array syntax. Its behavior is simple: it multiplies, then sums the product of arrays. In the example shown, the expression B5:B11 = C5:C11 will generate an array that contains TRUE and FALSE values like this:
Note that we have 3 TRUE values because there are 3 matches.
In this state, SUMPRODUCT will actually return zero because TRUE and FALSE values are not counted as numbers in Excel by default. To get SUMPRODUCT to treat TRUE as 1 and FALSE as zero, we need to “coerce” them into numbers. The double negative is a simple way to do that:--(B5:B11=C5:C11)
After coercion, we have:
With no other arrays to multiply, SUMPRODUCT simply sums the values and returns 3.
To count non-matching values, you can reverse the logic like so:=SUMPRODUCT(--(B5:B11<>C5:C11))