# How to count Yes and No answers in Excel

Use one COUNTIF per answer: `=COUNTIF($B$2:$B$12,E2)` with "Yes" in E2 and "No" in E3, filled down.

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

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Guest | Attending |  |  | Answer | Count |
| Lena | Yes |  |  | Yes |  |
| Omar | No |  |  | No |  |
| Noor | Yes |  |  |  |  |
| Tom | Yes |  |  |  |  |
| Priya |  |  |  |  |  |
| Ines | No |  |  |  |  |
| Jon | Yes |  |  |  |  |
| Mia | yes |  |  |  |  |
| Raj | No |  |  |  |  |
| Sara | Yes |  |  |  |  |
| Ali | Y |  |  |  |  |

Formula in F2, filled to F3: `=COUNTIF($B$2:$B$12,E2)`

Result: 6; 3

## How it works

COUNTIF counts the answers equal to the word next to it. It ignores case, so Mia's "yes" counts as Yes. The range is locked with $ so it stays the same when you fill down to the No row.

Ali's "Y" is neither Yes nor No, and Priya hasn't answered, so the two counts add up to 9 of 11 guests.

## Other ways

- `=SUMPRODUCT(--(TRIM($B$2:$B$12)=E2))`: Also catches answers with stray spaces, like "Yes ".
- `=COUNTIF($B$2:$B$12,LEFT(E2,1)&"*")`: Counts anything starting with Y (or N), so "Y" and "yes please" are included. (Also counts Ali's "Y" as a Yes.)

## Common mistakes

- Free-typed answers drift ("Y", "yes ", "Yeah"). Use a drop-down list (Data validation) so only Yes and No can be entered.
- Yes plus No may not equal the number of guests: blanks and other spellings fall in neither count. Check with `=COUNTBLANK(B2:B12)`.

## Questions

**How do I show the percentage of Yes answers?**

Divide by the number of answers: `=COUNTIF(B2:B12,"Yes")/COUNTA(B2:B12)`, formatted as a percentage.

**How do I count Yes answers for one group?**

Use COUNTIFS: `=COUNTIFS(B2:B12,"Yes",C2:C12,"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/count-yes-and-no-answers