Excel Percentage of Yes and No: How to Count

This page shows you how to calculate the percentage of “Yes” and “No” responses in Excel, and it also includes a quick calculator that works out the percentage from your counts. You enter the number of Yes and No answers, and you get back the percentage for each, plus the total count. The formula is simple: divide the count of each answer by the total number of responses and multiply by 100.

Formula

To find the percentage of “Yes” answers, use this formula:

Percentage of Yes = (Number of Yes ÷ Total Responses) × 100

Where:

• Number of Yes – the count of cells containing “Yes” (or your chosen value)
• Total Responses – the sum of all Yes and No counts (or all non-empty cells in the range)

For “No”, replace the numerator with the number of No answers. The result is a percentage, usually formatted with one or two decimal places.

Worked Example

Using the default values from the calculator (25 Yes and 15 No):

1. Total responses = 25 + 15 = 40
2. Percentage of Yes = (25 ÷ 40) × 100 = 62.5%
3. Percentage of No = (15 ÷ 40) × 100 = 37.5%

The calculator rounds to one decimal place, so it shows 62.5% and 37.5%, which matches the manual calculation.

Why You Might Need This

You often have a column of survey answers, feedback forms, or test results that contain only “Yes” and “No”. Instead of counting by hand, Excel can do it for you. This calculation is useful for:

• Summarizing survey responses (e.g., “Did you find this helpful?”)
• Tracking task completion (e.g., “Is the task done?”)
• Analyzing attendance or participation (e.g., “Did you attend?”)
• Reporting on pass/fail outcomes (e.g., “Did the test pass?”)

Knowing the percentage helps you see the proportion at a glance, which is often more meaningful than raw counts.

How to Do It in Excel (Step by Step)

If you have a column of data (say, A2:A100) containing “Yes” and “No”, here’s how to get the percentages:

1. Count the Yes answers – Use =COUNTIF(A2:A100,"Yes") in a cell.
2. Count the No answers – Use =COUNTIF(A2:A100,"No") in another cell.
3. Get the total – Add the two counts: =B1+B2 (if B1 and B2 hold the counts).
4. Calculate the percentage – Divide each count by the total and format as a percentage: =B1/B3 and =B2/B3.
5. Format as percent – Select the cells with the formulas, go to the Home tab, and click the Percent Style button (or press Ctrl+Shift+%).

If your data has other values (like blanks or “Maybe”), you should decide whether to include them. The formula above only counts exact matches, so blanks are ignored automatically.

Common Mistakes and Misconceptions

A frequent error is forgetting to format the result as a percentage, which makes Excel show a decimal like 0.625 instead of 62.5%. Another is using COUNTIF with extra spaces – for example, if your data has “Yes ” with a trailing space, the formula won’t match. Use =COUNTIF(A:A,"*Yes*") if you need to catch variations, but be aware that this also matches “Yes please” or “Not Yes”.

Another misconception is that you must include blanks in the total. If you only want the percentage among valid Yes/No responses, use the sum of the two counts as the denominator. If you want the percentage of all rows (including blanks), use =COUNTA(A2:A100) as the denominator.

How to Read the Result

The percentage tells you the relative frequency of each answer. For example, 62.5% Yes means that out of every 100 responses, about 63 are Yes. The total count helps you judge reliability – a percentage based on 10 responses is less stable than one based on 1,000. Always report the total alongside the percentage for context.

Limitations of This Calculation

This method assumes your data is categorical with only two possible values. If you have more than two answers (e.g., “Maybe”), the percentage of Yes and No won’t sum to 100% unless you include the other categories. Also, the formula treats text exactly – it is case-insensitive by default, but it does not trim spaces. If your data is numeric (1 for Yes, 0 for No), you can use =SUM(range) for the Yes count and =COUNT(range)-SUM(range) for No.

Frequently Asked Questions

How do I count yes and no in Excel?

Use =COUNTIF(range,"Yes") and =COUNTIF(range,"No"). Replace “range” with the actual cell range, like A2:A100. This counts only exact matches.

How do I calculate the percentage of yes responses in Excel?

Divide the count of Yes by the total count of Yes and No, then format as a percentage. For example, if B1 has the Yes count and B2 has the No count, use =B1/(B1+B2) and apply percent formatting.

Can I use COUNTIF to count yes and no in one formula?

Yes, you can use =COUNTIF(range,"Yes")+COUNTIF(range,"No") to get the total of both. But for individual percentages, you still need separate counts.

What if my data has “YES” and “yes” – will it count both?

Yes, COUNTIF is not case-sensitive, so it treats “YES”, “Yes”, and “yes” as the same. But it does not ignore spaces, so “Yes ” (with a space) won’t be counted.

How do I show the percentage as a number instead of a decimal?

After entering the division formula, select the cell, go to the Home tab, and click the Percent Style button. You can also use the keyboard shortcut Ctrl+Shift+%.

Does this work in Google Sheets too?

Yes, the COUNTIF and percentage formulas work the same way in Google Sheets. The steps are identical.

Related

• Fish It Admin Abuse Times? Facts & Context
• How to Choose Dishwasher Size, Wash Programs & Water Use
• Word Processor File Formats Explained

in yes | Yes count | - | 25 | 0
in no | No count | - | 15 | 0
out pct_yes | Yes percentage | % | yes / (yes + no) * 100 | 1
out pct_no | No percentage | % | no / (yes + no) * 100 | 1
out total | Total responses | - | yes + no | 0