How to count rows where one column is greater than another in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--(C2:C11>B2:B11))Compare the two columns directly inside SUMPRODUCT: =SUMPRODUCT(--(C2:C11>B2:B11)).
- Result on the example
- 5 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
C2:C11>B2:B11 compares each department's actual with its own budget, row by row, and returns TRUE where it is higher. SUMPRODUCT counts the TRUEs: five departments overspent.
Finance spent exactly its budget, so it isn't counted.
=SUMPRODUCT(--(C2:C11>B2:B11))Result5
C2:C1110 rows × 1 col: $54,200, $76,500, $131,900, $29,800, …A range: 10 cells in one column.
B2:B1110 rows × 1 col: $50,000, $80,000, $120,000, $30,000, …A range: 10 cells in one column.
C2:C11>B2:B11{10 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The > operator tests greater than.
-(C2:C11>B2:B11{10 rows × 1 col} -1, 0, -1, 0, …Makes the value negative.
--(C2:C11>B2:B11{10 rows × 1 col} 1, 0, 1, 0, …Makes the value negative.
SUMPRODUCT(--(C2:C11>B2:B11))5SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--(C2:C11>B2:B11{10 rows × 1 col} 1, 0, 1, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--(C2:C11>B2:B11)). We ran it in Google Sheets mode on the same data and it returns 5.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(--(C2:C11>B2:B11)) | 5 | Excel 2021, Microsoft 365 and Google Sheets handle the array in SUM directly. |
| =SUMPRODUCT(--(C2:C11>=B2:B11)) | 6Different result | Counts departments at or over budget. Also counts Finance, which spent exactly its budget. |
Common mistakes
=COUNTIF(C2:C11,">"&B2:B11)doesn't compare row by row; it checks the whole column against each budget and gives a misleading result.- Blank actuals count as 0 and are never over budget, which may hide missing data.
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 one column is greater than another
F1, count the departments whose actual spending is greater than their budget.Questions
How do I count rows more than 10% over budget?
Scale the budget: =SUMPRODUCT(--(C2:C11>B2:B11*1.1)).
Does this work with dates, like shipped after due?
Yes: =SUMPRODUCT(--(D2:D11>C2:C11)) counts late shipments when C is the due date and D the ship date.
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.