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:
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:
After coercion, we have:
With no other arrays to multiply, SUMPRODUCT simply sums the values and returns 3.
Count non-matching rows
To count non-matching values, you can reverse the logic like so:
The COUNTIFS function takes multiple criteria in pairs — each pair contains one range and the associated criteria for that range. To generate a count, all conditions must match. To add more conditions, just add another range / criteria pair...
The COUNTA function counts non-blank cells that contain numbers or text. The first COUNTA counts non-blank cells in the range B5:B11 and returns the number 7: COUNTA ( B5:B11 ) // returns 7 The second COUNTA function does the same with the range C5:...
Suppose you have a "master" list of some kind, and also have another list that contains some of the same items. You want a formula that compares the values in the 2nd list to see how many of them appear in the first list. You don't care about the...
The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an incredibly versatile function that can be used to count and sum like COUNTIFS or SUMIFS, but with more...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.