Count the Number of Yes in Excel (Using COUNTIF)

by

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:

Survey data

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:

COUNTIF formula to count yes in the data

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)
COUNTIF formula where criteria is in a cell

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.

Drag down the formula and it adjusts
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. 

Dataset with checkboxes as survey result

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:

  1. 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.
Insert a new helper column
  1. Next, Right-click on one of the checkboxes and select Format Control from the context menu.
Click on Format control
  1. This opens the Format Object dialog box. Click on the Control tab.
Click on Control
  1. Click on the cell reference button to the right of the ‘Cell link’ input box.
Click on the range selector icon
  1. 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.
Select the adjacent cell for the checkbox
  1. Click OK to close the dialog box.
  2. 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.
TRUE shows up when checkbox is selected
  1. Repeat the above steps to link each checkbox to a cell in column C.
  2. 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.
Helper column shows true and false for checkboxes

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.

COUNT Trues to Get the number of checked boxes

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)
Drag the same formula to count number of No

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

2 thoughts on “Count the Number of Yes in Excel (Using COUNTIF)”

  1. 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?

    Reply

    • 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

      Reply

Leave a Comment