How to count Yes and No answers in Excel
Formula (Excel and Google Sheets)
=COUNTIF($B$2:$B$12,E2)Use one COUNTIF per answer: =COUNTIF($B$2:$B$12,E2) with "Yes" in E2 and "No" in E3, filled down.
- Result on the example
- one result per row in
F2, filled down toF3 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2, filled down to F3. Change any input and the result updates, or edit the formula to experiment.
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.
=COUNTIF($B$2:$B$12,E2)Result6
$B$2:$B$1211 rows × 1 col: "Yes", "No", "Yes", "Yes", …A range: 11 cells in one column.
E2"Yes"The value in E2.
COUNTIF($B$2:$B$12,E2)6COUNTIF: Counts cells that meet one condition.- range
$B$2:$B$1211 rows × 1 col: "Yes", "No", "Yes", "Yes", … - criteria
E2"Yes"
- range
In Google Sheets
The same formula works in Google Sheets: =COUNTIF($B$2:$B$12,E2). 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 |
|---|---|---|
| =SUMPRODUCT(--(TRIM($B$2:$B$12)=E2)) | 6, 3 | Also catches answers with stray spaces, like "Yes ". |
| =COUNTIF($B$2:$B$12,LEFT(E2,1)&"*") | 7, 3Different result | 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).
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.
Count Yes and No answers
F2, count the guests whose answer matches E2, then fill down to F3.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").
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.