Spreadsheet Practice

How to calculate the percentage of Yes responses in Excel

Formula (Excel and Google Sheets)

=COUNTIF(B2:B13,"Yes")/COUNTA(B2:B13)

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

Result on the example
70.0% in E1
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.

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.

=COUNTIF(B2:B13,"Yes")/COUNTA(B2:B13)

Result0.7

  1. B2:B1312 rows × 1 col: "Yes", "No", "Yes", "Yes", …

    A range: 12 cells in one column.

  2. COUNTIF(B2:B13,"Yes")7

    COUNTIF: Counts cells that meet one condition.

    • rangeB2:B1312 rows × 1 col: "Yes", "No", "Yes", "Yes", …
    • criteria"Yes"
  3. COUNTA(B2:B13)10

    COUNTA: Counts cells that are not empty.

    • value1B2:B1312 rows × 1 col: "Yes", "No", "Yes", "Yes", …
  4. COUNTIF(B2:B13,"Yes")/COUNTA(B2:B13)0.7

    The / operator divides.

In Google Sheets

The same formula works in Google Sheets: =COUNTIF(B2:B13,"Yes")/COUNTA(B2:B13). We ran it in Google Sheets mode on the same data and it returns 70.0%.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=COUNTIF(B2:B13,"Yes")/ROWS(B2:B13)58.3%Different resultUses 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*".

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.

Calculate the percentage of Yes responses

In E1, calculate the percentage of answers in B2:B13 that are Yes, leaving out people who didn't answer.

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").

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.