How to calculate the percentage of cells that meet a condition in Excel
Formula (Excel and Google Sheets)
=COUNTIF(B2:B13,">="&E1)/COUNT(B2:B13)Count the cells that meet it and divide by the count of all cells: =COUNTIF(B2:B13,">="&E1)/COUNT(B2:B13).
- Result on the example
- 66.7% in
E2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
COUNTIF counts the scores at or above 70: 8. COUNT counts all the scores: 12. Dividing gives the pass rate, 66.7%. Using COUNT for the total means blank or text entries don't count against the class.
=COUNTIF(B2:B13,">="&E1)/COUNT(B2:B13)Result0.6666666667
B2:B1312 rows × 1 col: 82, 64, 91, 70, …A range: 12 cells in one column.
E170The value in E1.
">="&E1">=70"The & operator joins as text.
COUNTIF(B2:B13,">="&E1)8COUNTIF: Counts cells that meet one condition.- range
B2:B1312 rows × 1 col: 82, 64, 91, 70, … - criteria
">="&E1">=70"
- range
COUNT(B2:B13)12COUNT: Counts cells that contain numbers.- value1
B2:B1312 rows × 1 col: 82, 64, 91, 70, …
- value1
COUNTIF(B2:B13,">="&E1)/COUNT(B2:B13)0.6666666667The / operator divides.
In Google Sheets
The same formula works in Google Sheets: =COUNTIF(B2:B13,">="&E1)/COUNT(B2:B13). We ran it in Google Sheets mode on the same data and it returns 66.7%.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(--(B2:B13>=E1))/ROWS(B2:B13) | 66.7% | Divides by every row, which suits conditions COUNTIF can't express. |
| =AVERAGE(--(B2:B13>=E1))Excel only | 66.7% | Excel 2021 and Microsoft 365: the average of 1s and 0s is the share of 1s. |
Common mistakes
- Dividing by COUNTA includes text like "Absent" in the total, which lowers the rate. Choose the base on purpose.
- The criteria needs the operator in quotes joined to the cell:
">="&E1.
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 cells that meet a condition
E2, calculate the percentage of students who scored at least the pass mark in E1.Questions
How do I calculate the percentage with two conditions?
Use COUNTIFS on top: =COUNTIFS(B2:B13,">="&E1,C2:C13,"Math")/COUNTIF(C2:C13,"Math").
How do I show the percentage that failed?
Subtract from 1: =1-E2.
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.