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:
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:
How to Count Specific Words in Excel
How to Count Unique Values by Group in Excel
How to Use COUNTIF with Multiple Ranges in Excel
