Excel is quite commonly used in processing survey data and results.
In many cases, these surveys are used to collect user responses as Yes or No. and then as part of the survey data processing, you need to find the percentage or number of times the word ‘yes’ appears in the answers.
The ‘Yes’ could be in a text form if imported from an external application or form. It could also be in the form of a ticked checkbox (which is more user-friendly).
In this tutorial, we will look at how you can count the number of ‘yes’ in Excel (when the ‘yes’ appears in text form, as well as when it appears in a checkbox).
For both cases we are going to use the COUNTIF formula, so let us familiarize ourselves with this function first.
A Quick Overvew of the COUNTIF Function
The COUNTIF function is used to count the number of cells in a given range that match a given condition. The syntax for this function is as follows:
COUNTIF(range,condition)
Here,
- range is the range of cells that you want your function to work on or count
- condition is the criterion that should be satisfied for a cell in range to be included in the count
The COUNTIF function basically goes through each cell in the range and counts only those cells that match the given condition.
Count the Number of Yes in Excel (When written as Text String)
Let us first look at the case where the ‘Yes’ in the cells is in text form.
Consider the following dataset:
The list contains answers given by people about whether they will attend an event. From here, say we want to count how many people are coming for the event.
We can use the COUNTIF function in this case, to count the number of cells in the range B2:B9 that contain the text “yes”. Enter the following formula in cell E3:
=COUNTIF(B2:B9,"Yes")
Here’s the result we get:
Note: The COUNTIF function is case-insensitive. So, it doesn’t matter if your cell contains the string “Yes” in capital or small letters. For example, notice that cell B4 contains the value “yes” in all small letters, but it is still considered in the count.
You can similarly count the number of “no” by replacing it in the second parameter of the COUNTIF function, as follows:
=COUNTIF(B2:B9,"No")
Alternatively, a smarter approach in our example would be to use the cell reference of the string in the second parameter, instead of the actual text value.
This means, in cell E3, we could simply use the cell reference of the cell D3 (which in turn contains the text “yes”) as follows:
=COUNTIF(B2:B9,D3)
This will make things easier and quicker for us, as we don’t need to rewrite the formula in each cell of column D.
Instead, we can simply drag the fill handle (the little square at the bottom-right corner of cell E3) all the way down to cell E5.
This will ensure that the formula is copied down to the other cells while adjusting the cell references for each row.
When you click on cell E4 now, notice that the formula adjusted the reference in the second parameter to D4.
Also read: Count Cells Less than a Value in Excel (COUNTIF Less)
Count Yes in Excel (When as Ticked Checkbox)
Now let us look at a case where you have a list of checkboxes, instead of the text “yes” and “no”.
Consider the following screenshot, where column B contains checkboxes instead of text.
To find out how many people are coming, we will need to count the numbers of boxes that have been checked.
Unfortunately, you cannot use a formula in Excel that works directly with checkboxes. However, there is a way around this.
In our example, you need to create a separate ‘Helper’ column. The cells of this column will be linked to the checkboxes, such that whenever a checkbox is checked, the cell linked to it will show a TRUE value. The cell will show a FALSE value otherwise.
Once we get a column with TRUE and FALSE values, it becomes really easy to count them using the COUNTIF function.
Let us look at this process step by step:
- Create a new column next to column B. For this, simply right-click on the column C header and select ‘Insert’ from the context menu that appears.
- Next, Right-click on one of the checkboxes and select Format Control from the context menu.
- This opens the Format Object dialog box. Click on the Control tab.
- Click on the cell reference button to the right of the ‘Cell link’ input box.
- Select the blank cell corresponding to your selected checkbox (cell C2, if your selected checkbox was in cell B2). This will be the cell that will be linked to the checkbox.
- Click OK to close the dialog box.
- You will now see a TRUE value displayed in the linked cell when your selected checkbox is checked and the value will become FALSE when it is unchecked.
- Repeat the above steps to link each checkbox to a cell in column C.
- You should now get a list of TRUE and FALSE values in column D that will change according to the checked status of their corresponding cell in column B.
Once your column C is ready, all that’s left to do is count the number of TRUE values in it. For this, you can use the COUNTIF function in cell F4 as follows:
=COUNTIF(C2:C9,TRUE)
Notice we did not use double quotes for the second parameter. This is because TRUE and FALSE are not text but Boolean values. So, you cannot treat them as strings.
Similarly, if you want to count the number of unchecked boxes and display the number in cell F5, simply use the same formula, but replace the TRUE in the second parameter with FALSE, as shown below:
=COUNTIF(C2:C9,FALSE)
In this tutorial, we showed you how to use the COUNTIF formula to count the number of “Yes” in two cases – when the yes appears as text, and when it appears as a checked checkbox.
Both are quite simple solutions, so we hope you found them useful and easy to follow.
Other Excel tutorials you may also like:
- How to Count Negative Numbers in Excel
- How to Count How Many Times a Word Appears in Excel
- How to Flip Data in Excel (Columns, Rows, Tables)?
- How to Compare Two Columns in Excel (using VLOOKUP & IF)
- How to use Excel If Statement with Multiple Conditions Range [AND/OR]
- Multiple If Statements in Excel (Nested Ifs, AND/OR) with Examples
- How to Count Unique Values in Excel (5 Easy Formulas)
- COUNTIF Greater Than Zero in Excel
- How to Count Cells with Text in Excel?
- Count Cells that are Not Blank in Excel (Formulas)
Hi Steve,
Thank you for creating this platform to share your wisdom and knowledge. I am quite new to use Excel for my data set. I tried hard to count “yes” responses in my data set but it doesn’t work. I watched many videos and followed all the instructions but unfortunately couldn’t manage to get the outcome.
Can you help me please?
Hello Shazia… Can you share sample data with me on steve@spreadsheetplanet.com. One possible issue I can think of can be that the cells that have the values ‘Yes’ have leading or trailing spaces, which may trick the formulas in thinking that it’s not a Yes. There could be more issues, but I can only investigate it once I see the dataset