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:
If you want to count rows where two (or more) criteria match, you can use a formula based on the COUNTIFS function. In the example shown, we want to count the number of orders with a color of "blue" and a quantity > 15. The formula we have in cell...
If you have a list of items, and need to count how many you have total, how many are sold, how remain, etc., you can use the COUNTA function. This can be useful if you are selling tickets, seats, entries, or anything where you maintain and track an...
If you want to compare two ranges, and count total matches between the two ranges, you can use a formula that combines the COUNTIF and SUMPRODUCT functions. Context Suppose you have a "master" list of some kind, and also have another list that...
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.