How to calculate the percentage of each answer in a survey in Excel
Formula (Excel and Google Sheets)
=COUNTIF($B$2:$B$16,E2)/COUNTA($B$2:$B$16)Count each answer and divide by all answers: =COUNTIF($B$2:$B$16,E2)/COUNTA($B$2:$B$16), filled down the list of answers.
- Result on the example
- one result per row in
F2, filled down toF6 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2, filled down to F6. Change any input and the result updates, or edit the formula to experiment.
How it works
COUNTIF counts the responses that match the answer next to the formula, and COUNTA counts all responses, 15. The response range is locked with $ so it stays put while E2 moves down the list. The five shares add up to 100%.
40% chose "Satisfied", the most common answer.
=COUNTIF($B$2:$B$16,E2)/COUNTA($B$2:$B$16)Result0.3333333333
$B$2:$B$1615 rows × 1 col: "Very satisfied", "Satisfied", "Satisfied", "Neutral", …A range: 15 cells in one column.
E2"Very satisfied"The value in E2.
COUNTIF($B$2:$B$16,E2)5COUNTIF: Counts cells that meet one condition.- range
$B$2:$B$1615 rows × 1 col: "Very satisfied", "Satisfied", "Satisfied", "Neutral", … - criteria
E2"Very satisfied"
- range
COUNTA($B$2:$B$16)15COUNTA: Counts cells that are not empty.- value1
$B$2:$B$1615 rows × 1 col: "Very satisfied", "Satisfied", "Satisfied", "Neutral", …
- value1
COUNTIF($B$2:$B$16,E2)/COUNTA($B$2:$B$16)0.3333333333The / operator divides.
In Google Sheets
The same formula works in Google Sheets: =COUNTIF($B$2:$B$16,E2)/COUNTA($B$2:$B$16). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =COUNTIF($B$2:$B$16,E2)/ROWS($B$2:$B$16) | 33.3%, 40.0%, 13.3%… | Divides by everyone surveyed, so skipped questions lower every share. Same here because nobody skipped. |
Common mistakes
- An answer typed differently ("Satisfied " with a space) is missed and the shares add up to less than 100%. Check with
=SUM(F2:F6). - Use the same answer labels in E as in the data, or COUNTIF finds nothing.
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 each answer in a survey
F2, calculate the share of responses that gave the answer in E2, then fill down to F6.Questions
How do I list the answers automatically?
In Excel 365 or Google Sheets, put =UNIQUE(B2:B16) in E2.
How do I calculate a "top two box" score?
Add the two best answers: =(COUNTIF(B2:B16,"Very satisfied")+COUNTIF(B2:B16,"Satisfied"))/COUNTA(B2:B16).
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.