# How to calculate the percentage of Yes responses in Excel

Count the Yes answers and divide by the number of answers: `=COUNTIF(B2:B13,"Yes")/COUNTA(B2:B13)`.

**Excel:** `=COUNTIF(B2:B13,"Yes")/COUNTA(B2:B13)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Respondent | Would recommend? |  | Yes rate |
| R-01 | Yes |  |  |
| R-02 | No |  |  |
| R-03 | Yes |  |  |
| R-04 | Yes |  |  |
| R-05 |  |  |  |
| R-06 | No |  |  |
| R-07 | Yes |  |  |
| R-08 | Yes |  |  |
| R-09 | No |  |  |
| R-10 |  |  |  |
| R-11 | Yes |  |  |
| R-12 | Yes |  |  |

Formula in E1: `=COUNTIF(B2:B13,"Yes")/COUNTA(B2:B13)`

Result: 70.0%

## How it works

COUNTIF counts the 7 Yes answers. COUNTA counts the cells that have any answer, 10, so the two blank rows are left out of the total. 7 out of 10 is 70%.

COUNTIF ignores case, so "yes" counts too.

## Other ways

- `=COUNTIF(B2:B13,"Yes")/ROWS(B2:B13)`: Uses everyone surveyed as the base, so blanks count as not Yes. (Returns 58.3%: 7 out of all 12 respondents.)

## Common mistakes

- Decide the base: answers given (COUNTA) or people asked (ROWS). Reports that mix them disagree.
- Variants like "Y" or "Yes " don't match "Yes". Use a drop-down list, or count `"Y*"`.

## Questions

**How do I calculate the percentage of No answers?**

Change the word: `=COUNTIF(B2:B13,"No")/COUNTA(B2:B13)`.

**How do I get the Yes rate for one group?**

Use COUNTIFS for both parts: `=COUNTIFS(B2:B13,"Yes",C2:C13,"Team A")/COUNTIFS(B2:B13,"<>",C2:C13,"Team A")`.

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-yes-responses