How to calculate the percentage of projects within budget in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--(C2:C11<=B2:B11))/ROWS(B2:B11)Count the rows where actual is at most budget, then divide by the number of projects: =SUMPRODUCT(--(C2:C11<=B2:B11))/ROWS(B2:B11).
- Result on the example
- 70% 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 project's actual cost with its own budget and returns TRUE when it is on or under budget. SUMPRODUCT counts the TRUEs (7) and ROWS counts the projects (10): 70% came in within budget. P-03 hit its budget exactly and counts as within.
=SUMPRODUCT(--(C2:C11<=B2:B11))/ROWS(B2:B11)Result0.7
C2:C1110 rows × 1 col: $47,800, $131,500, $30,000, $69,900, …A range: 10 cells in one column.
B2:B1110 rows × 1 col: $50,000, $120,000, $30,000, $75,000, …A range: 10 cells in one column.
C2:C11<=B2:B11{10 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …The <= operator tests at most.
-(C2:C11<=B2:B11{10 rows × 1 col} -1, 0, -1, -1, …Makes the value negative.
--(C2:C11<=B2:B11{10 rows × 1 col} 1, 0, 1, 1, …Makes the value negative.
SUMPRODUCT(--(C2:C11<=B2:B11))7SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--(C2:C11<=B2:B11{10 rows × 1 col} 1, 0, 1, 1, …
- array1
ROWS(B2:B11)10ROWS: Number of rows in a range.- array
B2:B1110 rows × 1 col: $50,000, $120,000, $30,000, $75,000, …
- array
SUMPRODUCT(--(C2:C11<=B2:B11))/ROWS(B2:B11)0.7The / operator divides.
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--(C2:C11<=B2:B11))/ROWS(B2:B11). We ran it in Google Sheets mode on the same data and it returns 70%.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =AVERAGE(--(C2:C11<=B2:B11))Excel only | 70% | Excel 2021 and Microsoft 365: the average of 1s and 0s is the share of 1s. |
Common mistakes
- COUNTIF can't compare two columns row by row, which is why the formula uses SUMPRODUCT.
- Projects without an actual yet count as within budget, because a blank is 0. Exclude them with
*(C2:C11<>"")and divide byCOUNT(C2:C11).
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.
Calculate the percentage of projects within budget
F1, calculate the percentage of projects whose actual cost is no more than their budget.Questions
How do I allow a 5% buffer?
Scale the budget: =SUMPRODUCT(--(C2:C11<=B2:B11*1.05))/ROWS(B2:B11).
How do I get the share of the total budget that was overspent?
Divide total overruns by total budget: =SUMPRODUCT((C2:C11>B2:B11)*(C2:C11-B2:B11))/SUM(B2:B11).
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.