Count the Number of Yes in Excel (Using COUNTIF)

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

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: