How to count rows where two columns match in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--(B2:B11=C2:C11))Compare the columns row by row and add up the matches: =SUMPRODUCT(--(B2:B11=C2:C11)).
- Result on the example
- 6 in
F1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F1. Change any input and the result updates, or edit the formula to experiment.
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.
=SUMPRODUCT(--(B2:B11=C2:C11))Result6
B2:B1110 rows × 1 col: 120, 45, 300, 18, …A range: 10 cells in one column.
C2:C1110 rows × 1 col: 120, 44, 300, 18, …A range: 10 cells in one column.
B2:B11=C2:C11{10 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …The = operator tests whether the two sides are equal.
-(B2:B11=C2:C11{10 rows × 1 col} -1, 0, -1, -1, …Makes the value negative.
--(B2:B11=C2:C11{10 rows × 1 col} 1, 0, 1, 1, …Makes the value negative.
SUMPRODUCT(--(B2:B11=C2:C11))6SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--(B2:B11=C2:C11{10 rows × 1 col} 1, 0, 1, 1, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--(B2:B11=C2:C11)). We ran it in Google Sheets mode on the same data and it returns 6.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(--(B2:B11=C2:C11)) | 6 | In Excel 2021, Microsoft 365 and Google Sheets, SUM handles the array directly. |
| =SUMPRODUCT(--(B2:B11<>C2:C11)) | 4Different result | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count rows where two columns match
F1, count the SKUs where the system quantity equals the counted quantity.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)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.