# How to calculate the percentage of each answer in a survey in Excel

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.

**Excel:** `=COUNTIF($B$2:$B$16,E2)/COUNTA($B$2:$B$16)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Respondent | Satisfaction |  |  | Answer | Share |
| R-01 | Very satisfied |  |  | Very satisfied |  |
| R-02 | Satisfied |  |  | Satisfied |  |
| R-03 | Satisfied |  |  | Neutral |  |
| R-04 | Neutral |  |  | Dissatisfied |  |
| R-05 | Very satisfied |  |  | Very dissatisfied |  |
| R-06 | Satisfied |  |  |  |  |
| R-07 | Dissatisfied |  |  |  |  |
| R-08 | Satisfied |  |  |  |  |
| R-09 | Very satisfied |  |  |  |  |
| R-10 | Neutral |  |  |  |  |
| R-11 | Satisfied |  |  |  |  |
| R-12 | Very satisfied |  |  |  |  |
| R-13 | Very dissatisfied |  |  |  |  |
| R-14 | Satisfied |  |  |  |  |
| R-15 | Very satisfied |  |  |  |  |

Formula in F2, filled to F6: `=COUNTIF($B$2:$B$16,E2)/COUNTA($B$2:$B$16)`

Result: 33.3%; 40.0%; 13.3%; 6.7%; 6.7%

## 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.

## Other ways

- `=COUNTIF($B$2:$B$16,E2)/ROWS($B$2:$B$16)`: 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/percentage-of-each-answer