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