How can I count the number of “Yes” and “No” values in a range using Excel?

By /

The process of counting the number of “Yes” and “No” values in a range using Excel involves utilizing the COUNTIF function. This function allows you to specify a certain criteria, in this case “Yes” or “No”, and then counts the number of cells in the specified range that meet that criteria. By using this function, you can easily and accurately determine the number of “Yes” and “No” values within a given range in your Excel spreadsheet.

Excel: Count Number of “Yes” and “No” Values in Range

You can use the following formulas to count the number of “Yes” and “No” values in a particular range in Excel:

Formula 1: Count Number of “Yes” Values

=COUNTIF(B2:B21, "Yes")

Formula 2: Count Number of “No” Values

=COUNTIF(B2:B21, "No")

Method 3: Count Percentage of “Yes” Values

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

Method 4: Count Percentage of “No” Values

=COUNTIF(B2:B21, "No")/COUNTA(B2:B21)

The following example shows how to use each formula in practice with the following dataset in Excel that shows whether or not various students passed a particular course:

Example: How to Count “Yes” and “No” Values in Excel

We can type the following formulas in the following cells to count the number of “Yes” and “No” values in the range B2:B21:

  • E1: =COUNTIF(B2:B21, “Yes”)
  • E2: =COUNTIF(B2:B21, “No”)
  • E3: =COUNTIF(B2:B21, “Yes”)/COUNTA(B2:B21)
  • E4: =COUNTIF(B2:B21, “No”)/COUNTA(B2:B21)

The following screenshot shows how to use these formulas in practice:

Excel count Yes and No values

From the output we can see:

  • There were 9 total “Yes” values in column B.
  • There were 11 total “No” values in column B.
  • A total of 45% of cells were equal to “Yes” in column B.
  • A total of 55% of cells were equal to “No” in column B.

Note that the COUNTA function counts the number of cells in a range that are not empty.

By dividing the result of the COUNTIF function by the COUNTA function, we’re able to calculate the percentage of total values that are equal to either “Yes” or “No” in column B. 

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

x

Related terms:

  1. Excel: Count Number of “Yes” and “No” Values in Range
  2. How can I use Google Sheets to count the number of “Yes” and “No” values in a specified range?
  3. How can I count the number of “Yes” and “No” values in a range using Google Sheets?
  4. Student Question: Count Unique Values in Range Using VBA
  5. Excel: Apply Conditional Formatting to Yes/No Values
  6. How can I apply conditional formatting to Yes/No values in Google Sheets?
  7. How can I count the values in a column in PySpark, while also applying a condition on the column’s values?
  8. How can I use conditional formatting to highlight Yes/No values in Google Sheets?
  9. How can I use VLOOKUP to search for values within a specified date range in Excel?
  10. # Semi-Interquartile Range Calculator ## What is semi-interquartile range?