How to count Yes and No answers in Excel

Count Yes and No with COUNTIF

Ten people answered a post-workshop survey. Column C holds their answer to "Would you recommend it?". COUNTIF takes the range to check and the value to count, and the value goes in quotes because it is text.

COUNTIF is not case-sensitive, so Mei Chen's lowercase "yes" counts. Liam Walsh skipped the question, so his cell is empty and is in neither count.

Percent who said Yes

Decide what you are dividing by before you build the formula. "Percent of everyone surveyed" divides by all ten people. "Percent of people who answered" divides by the Yes plus No count, which is what most survey reports mean, because a skipped question is not a No.

Format the cell as a percentage (Home > Number > %). Avoid COUNTA(C2:C11) as the divisor: it counts any non-empty cell, including typos, "N/A" and cells that only hold a space.

Stray spaces: the answer COUNTIF misses

Grace Okafor's cell in C8 holds "Yes " with a space at the end. It looks identical on screen, but COUNTIF compares the whole text, so it is neither "Yes" nor "No". This is very common in form exports and in cells someone typed by hand.

You can catch it with a wildcard, =COUNTIF(C2:C11,"Yes*"), which also returns 6 here, but that also counts answers such as "Yes, if it were shorter". A safer check trims every cell before comparing. SUMPRODUCT handles the whole range and works in every version of Excel.

Count Yes per team with COUNTIFS

COUNTIFS takes pairs of range and value, and a row is counted only when every pair matches. Put the team names down a column of a summary table and point the formula at that cell instead of typing "Support", so one formula fills the whole table.

The trailing-space problem applies here too. Finance has two Yes answers on screen, but COUNTIFS finds only one because of C8. Clean the data once (see below) rather than patching every formula.

Checkboxes and TRUE/FALSE columns

A checkbox in Microsoft 365 (Insert > Checkbox) stores TRUE when ticked and FALSE when not, and older form controls can be linked to a cell that does the same. Count them with TRUE or FALSE as the criteria, with no quotes.

Do not try to SUM a TRUE/FALSE column: SUM ignores logical values in a range, so =SUM(D2:D11) returns 0 here. If you want to add them, convert them to numbers first with =SUMPRODUCT(--D2:D11), which returns 6.

Stop the problem at the source: a Yes/No drop-down

Every variant you have to catch ("Y", "yes ", "Yes!") comes from free typing. A drop-down list leaves only two possible values, so a plain COUNTIF stays correct. To fix the cells you already have, use Home > Find & Select > Replace, or a TRIM helper column pasted back as values.

1. Select C2:C11.
2. Go to Data > Data Validation.
3. Under Allow, choose List.
4. In Source, type Yes,No and click OK.
5. Each cell now shows a drop-down arrow, and typing anything else is rejected.