You can use the following syntax to count the number of matches between two columns in Excel:
=SUMPRODUCT(--(A2:A11=B2:B11))
This particular formula counts the number of matches between the range A2:A11 and the range B2:B11.
You can also use the following formula to count the number of non-matches between two columns:
=SUMPRODUCT(--(A2:A11<>B2:B11))
Note: The symbols <> stand for “not equal” in Excel.
The following example shows how to use these formulas in practice.
Example: Count Number of Matches Between Two Columns in Excel
Suppose we have the following two columns in Excel that contain the names of various basketball teams:
We can type the following formula into cell D2 to count the number of matching team names between columns A and B:
=SUMPRODUCT(--(A2:A11=B2:B11))
The following screenshot shows how to use this formula in practice:
The formula returns a value of 4.
We can manually confirm that this is correct by identifying each of the matching team names between the two columns:
We can also type the following formula into cell D2 to count the number of no-matching team names between columns A and B:
=SUMPRODUCT(--(A2:A11<>B2:B11))
The following screenshot shows how to use this formula in practice:
We can see that there are 6 teams that have non-matching names between the two columns.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Compare Two Lists in Excel Using VLOOKUP
How to Find Unique Values from Multiple Columns in Excel
How to Filter Multiple Columns in Excel
