Count Matches Between Two Columns in Excel

This guide explains how to count matches between two columns in Excel or Google Sheets, showing you the exact formulas to use and how to interpret the results. You’ll learn methods for counting exact matches, case-insensitive matches, and matches that appear anywhere in the other column.

Formula

For two columns of text values, the formula to count how many items in Column 1 appear anywhere in Column 2 is:

=SUMPRODUCT(--(COUNTIF(B:B, A:A)>0))

Here, A:A is the first column and B:B is the second column. The COUNTIF function checks each cell in A against the entire B column; if it finds at least one match, COUNTIF returns a number greater than 0. The double negative (– ) converts TRUE/FALSE to 1/0, and SUMPRODUCT adds them up. The result is the number of unique values in Column 1 that appear at least once in Column 2.

If you need to count matches that occur in the same row (e.g., A1 vs B1, A2 vs B2), use =SUMPRODUCT(--(A:A=B:B)) instead. That formula counts rows where the two cells are identical.

Worked Example

Suppose Column 1 contains: Apple, Banana, Cherry, Date, Elderberry. Column 2 contains: Banana, Fig, Grape, Apple, Kiwi. Using the formula above (with A1:A5 and B1:B5):

1. COUNTIF(B:B, A1) checks if “Apple” appears in B → yes (1)
2. COUNTIF(B:B, A2) checks “Banana” → yes (1)
3. COUNTIF(B:B, A3) checks “Cherry” → no (0)
4. COUNTIF(B:B, A4) checks “Date” → no (0)
5. COUNTIF(B:B, A5) checks “Elderberry” → no (0)

SUMPRODUCT adds: 1+1+0+0+0 = 2. The result is 2 matches, even though “Apple” and “Banana” appear in different positions.

How to Choose the Right Method

Different situations call for different formulas. Here’s a quick guide:

• Count unique matches from Column 1 that exist anywhere in Column 2 → use =SUMPRODUCT(--(COUNTIF(B:B, A:A)>0))
• Count rows where the two cells are identical → use =SUMPRODUCT(--(A:A=B:B))
• Count all occurrences (including duplicates) → use =SUMPRODUCT(COUNTIF(B:B, A:A)) – this counts each time a value in A appears in B, so if “Apple” appears twice in B, it counts 2.
• Case-sensitive match → use =SUMPRODUCT(--(EXACT(A:A, B:B))) for row-wise, or an array formula with MATCH and EXACT for column-wide.

Common Mistakes and Pitfalls

One frequent error is using =COUNTIF(A:A, B:B) alone, which only returns a single number for the first row, not a total. Another is forgetting that COUNTIF is case-insensitive by default; if you need exact case, you must use EXACT. Also, watch out for trailing spaces or non-printing characters—use TRIM and CLEAN to normalize data before counting. Finally, if your columns contain numbers stored as text, they won’t match numeric values; use the VALUE function or convert the column format.

Comparing Excel vs Google Sheets

Both Excel and Google Sheets support these formulas, but there are minor differences. In Google Sheets, you may need to use =ARRAYFORMULA when using SUMPRODUCT with entire columns, though SUMPRODUCT usually works directly. Excel handles full-column references fine, but if you have thousands of rows, limiting the range (e.g., A1:A1000) improves performance. Also, Excel’s COUNTIF is case-insensitive; Google Sheets is too, but both offer EXACT for case-sensitive matching.

Limitations and When Not to Use

These formulas work best with text or numbers that are exactly equal. They do not handle partial matches (e.g., “Apple” vs “Green Apple”)—for that, you’d need wildcards like =COUNTIF(B:B, "*"&A1&"*"), but that counts each row separately and can’t be easily summed across a column. Also, the unique-match formula counts each value in Column 1 only once, even if it appears multiple times in Column 2. If you need to count duplicates, use the all-occurrences version. For very large datasets (over 100,000 rows), consider using Power Query or a database instead of array formulas.

Frequently Asked Questions

How do I count matches between two columns in Excel?

Use =SUMPRODUCT(--(COUNTIF(B:B, A:A)>0)) to count unique values from Column A that appear anywhere in Column B. For row-by-row matches, use =SUMPRODUCT(--(A:A=B:B)).

How do I count matches in Google Sheets?

The same formulas work, but you may need to wrap them with ARRAYFORMULA if they don’t compute automatically. For example: =ARRAYFORMULA(SUMPRODUCT(--(COUNTIF(B:B, A:A)>0))).

How do I count case-sensitive matches?

Use =SUMPRODUCT(--(EXACT(A:A, B:B))) for row-wise. For column-wide unique matches, you’d need a more complex array formula with MATCH and EXACT, but it’s rarely necessary.

How do I count matches that contain partial text?

Use wildcards: =COUNTIF(B:B, "*"&A1&"*") for a single cell. To sum across a column, use =SUMPRODUCT(--(COUNTIF(B:B, "*"&A:A&"*")>0))—but note this may overcount if a value appears multiple times.

Why is my formula returning 0 when there are matches?

Check for leading/trailing spaces, non-printable characters, or number vs text formatting. Use TRIM and CLEAN to clean the data, and ensure both columns are the same data type.

Can I count matches between two columns in different sheets?

Yes, just reference the other sheet in the COUNTIF, e.g., =SUMPRODUCT(--(COUNTIF(Sheet2!B:B, A:A)>0)).

Related

• Samsung Secure Folder: What It Protects & Limits
• Claude Code Task Alerts: Bells & Desktop Notifications
• How to Add Conditional Formatting Rules in Excel

in a | Column 1 (text) | - | A | 0
in b | Column 2 (text) | - | B | 0
out matches | Matches count | - | sum(if(not(isna(match(a,b,0))),1,0)) | 0