# How to count rows where two columns match in Excel

Compare the columns row by row and add up the matches: `=SUMPRODUCT(--(B2:B11=C2:C11))`.

**Excel:** `=SUMPRODUCT(--(B2:B11=C2:C11))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| SKU | System qty | Counted qty |  | Matching rows |
| SKU-01 | 120 | 120 |  |  |
| SKU-02 | 45 | 44 |  |  |
| SKU-03 | 300 | 300 |  |  |
| SKU-04 | 18 | 18 |  |  |
| SKU-05 | 76 | 80 |  |  |
| SKU-06 | 0 | 0 |  |  |
| SKU-07 | 250 | 248 |  |  |
| SKU-08 | 64 | 64 |  |  |
| SKU-09 | 12 | 15 |  |  |
| SKU-10 | 90 | 90 |  |  |

Formula in F1: `=SUMPRODUCT(--(B2:B11=C2:C11))`

Result: 6

## How it works

`B2:B11=C2:C11` compares each row's pair of values and returns TRUE where they match. The double minus turns TRUE into 1, and SUMPRODUCT adds them: 6 of 10 SKUs agree with the system.

COUNTIF can't do a row-by-row comparison between two columns, which is why SUMPRODUCT is used.

## Other ways

- `=SUM(--(B2:B11=C2:C11))`: In Excel 2021, Microsoft 365 and Google Sheets, SUM handles the array directly.
- `=SUMPRODUCT(--(B2:B11<>C2:C11))`: Counts the mismatches, the rows to recount. (Counts the 4 SKUs that don't match.)

## Common mistakes

- Numbers stored as text never equal real numbers: "120" and 120 don't match. Convert one column if the counts come from different systems.
- Text comparisons ignore case. Use `=SUMPRODUCT(--EXACT(B2:B11,C2:C11))` if case matters.

## Questions

**How do I count matches within a tolerance?**

Compare the difference: `=SUMPRODUCT(--(ABS(B2:B11-C2:C11)<=2))` counts rows within 2 units.

**How do I count values in one list that appear anywhere in another?**

That is a lookup, not a row comparison: `=SUMPRODUCT(--(COUNTIF(C2:C11,B2:B11)>0))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-rows-where-two-columns-match