Are you looking for a way to count the number of Yes answers in Google Sheets?

There are a few different ways you can do this, and which one you choose will depend on how your data is set up.

Counting Yes values is one of the most basic tasks. You might want to count Yes values in your spreadsheet for many reasons. For example, you may need to find out how many people have responded to a survey or count how many tasks are done.

In this article, you will learn all the different ways how to count Yes in Google Sheets.

Built-in functions such as COUNTIFS, SUMPRODUCT, FILTER, QUERY, and SUBTOTAL with filter toggles can all be used to count Yes in Google Sheets.

Google Sheets features Column Stats and Explore will instantly display the number of Yes in your data.  Also, you can use Charts or Pivot tables to calculate the number of Yes values. You can even create an Apps Script Custom Function for this task.

This post explains step by step how to use each one of these methods with an example of Count Yes in Google Sheets.

Get your copy of the example workbook used in this post to follow along!

Count Yes with the COUNTIFS Function

You can use the COUNTIFS function in order to count how many yes responses there are.

= COUNTIFS ( criteria_range1, criterion1, [criteria_range2, …], [criterion2, …] )

The Google Sheets COUNTIFS function applies conditions to your data range and counts only the satisfied instances.

= COUNTIFS ( B2:B6, "yes" )

The above formula will count Yes values in the range B2:B6 with the COUNTIFS function.

Count Yes with the SUMPRODUCT Function

To count the number of yes responses in Google Sheets, you can use the SUMPRODUCT function.

This function multiplies corresponding cells from two ranges and then sums up all those products together.

= SUMPRODUCT ( array1, [array2, …] )

= SUMPRODUCT ( B2:B6 = "Yes" )

The above formula will count Yes values in the range B2:B6 with the SUMPRODUCT function.

This creates a range of true and false values where there is a true value generated for any Yes values in the range.

The SUMPRODUCT forces the Boolean values to be converted to 1 for true and 0 for false. These are summed and result in the count of Yes values from your range.

Count Yes with the FILTER Function

The FILTER function in Google Sheets allows you to filter a range of data by a specified condition. This is useful for data that changes often, as you can update the filter criteria and the data will be automatically updated.

You can use Google Sheets FILTER and COUNTA functions to count how many Yes in the given data range.

= COUNTA ( FILTER ( B2:B6, B2:B6 = "Yes" ) ) 

In this example, Your data is in range B2:B6 and needs to count only Yes.

You passed the same array B2:B6 as both the range to filter and the range for your condition.

The FILTER function will return an array of only the Yes values. The COUNTA is then used to count the number of returned items from the FILTER function.

The nested formula will count Yes values in the range B2:B6

Count Yes with the QUERY Function

The QUERY function in Google Sheets can be used to Count Yes responses.

The QUERY function can be used to perform a variety of operations such as filtering, sorting, aggregating, calculating, extracting, and transforming the data.

= QUERY ( data, query, [headers] )

= QUERY ( B2:B6, "SELECT COUNT(B) WHERE B = 'Yes'" ) 

The QUERY function takes three arguments a range of cells to query, an SQL query statement, and optional headers to indicate the number of header rows in your dataset.

The above QUERY function looks at the range B2:B6, counts the number of cells in the range that contain the text Yes, and returns the number to the below cell.

Count Yes with an Apps Script Custom Function

You can create a custom function with an App script to Count Yes in Google Sheets.  It will be very useful if you need to perform this task often. 

1. Select the Extensions menu 
2. Click Apps Script

The above steps open the apps script code editor window

function COUNTYES(range) {
  var yesCount = range.reduce(function (a, b) {
    return a + (b[0].toLowerCase() === 'yes' ? 1 : 0);
  }, 0);
  return yesCount
}

Copy and paste the above script into your Google Sheets code editor and press the Save button.

This code creates a function called COUNTYES(), which takes in an array range as an argument.

Inside the function, it reduces the elements of range by checking if the string starts with yes. If so, it adds 1 to a counter variable; otherwise, it adds 0.

In the end, it returns the total count of yes values in the range.

= COUNTYES ( B2:B6 )

You can now use the above formula in your sheet to count the number of yes values in your range B2:B6.

Count Yes with SUBTOTAL Function & Filter Toggles

The SUBTOTAL function in Google Sheets allows you to quickly aggregate your data using many different types of aggregation types.

The function can also aggregate your data in a filtered range of cells and show results for only the visible cells. The SUBTOTAL function in Google Sheets can help you count how many Yes are in a column.

= SUBTOTAL ( FunctionCode, range1, [range2], ... )

• FunctionCode – The function type to use in subtotal aggregation. For counting visible cells this is 103.
• range – The range over which to calculate a subtotal.

Here are the respective codes you can use this the SUBTOTAL function.

= SUBTOTAL ( 103, $B$2:$B$6 )

The above formula will calculate the count of visible values in your data array $B$2:$B$6.

The above formula applies the function COUNTA on your range B2:B6 and returns 5. It counts both Yes and No in your data, so you will need to filter on only the Yes values.

Please follow the below steps to Count only Yes using SUBTOTAL function & Filter Toggles

1. Go to cell B2
2. Press the Create a Filter toggle button to apply the filter to your data, you should then be able to see the filter dropdown icon in cells A1 and B1  

3. Select the filter drop-down icon in cell B1.
4. Make sure only Yes is selected in the item list. If both Yes and No are selected just uncheck No.
5. Press the OK button.

The number of Yes responses will be displayed in cell B8.

Count Yes with Column Stats

With the Google Sheets Column Stats feature, you can instantly see the most and least common values as well as visualizations for count and distribution, frequency tables, and summary statistics at the column level.

Please follow the below steps to Count Yes with Column Stats in Google Sheets

1. Go to cell B2 or select any cell in that column B where you want to count yes values.
2. Choose the Data menu in the ribbon.
3. Select Column stats from the options.

Google Sheets Column stats provide the details of the active column on the right side of your browser window

4. Please change Ignore rows to 1.  Since cell B1 contains the column heading text and you need to ignore it.

It gives you the number of unique items in that column with count and plots a bar graph for the same. Also, it provides the frequency table.

Count Yes with Explore

You may quickly and simply use visualization tools to evaluate your data using the Google Sheets Explore feature.

With a few clicks using Explore, you can quickly generate graphs and charts from their data. Additionally, Explore offers guidance on how to conduct additional analysis of your data.

It acts as a toggle button so you can hide/unhide the Explore feature.

Go to the sheet where you want to count yes values and press the Explore button. You can find the Explore button at the bottom right corner of your Google Sheets.

Once you click you can see the Explore window on the right side of your Google Sheets with all the details.

Explore take the entire worksheet data and provide you the complete analysis of the worksheet data. You can change the data by clicking the Edit button if you need.

Scroll down the explore pane and see the details in the Analysis section.

The pie chart shows the number of Yes / No of column B. Just hover the mouse over the pie and you can see more details. Also, the bar graph displays both Yes and No counts in the individual bar.

You have two useful icons in the Analysis section. Use the Graph icon to insert the displayed graphs in Google Sheets, or the Magnifine Lens icon to zoom the graphs to see in more detail.

How to Count Yes using the Answers section in Explore

Another way to get the count of yes values in the Explore window is with the ANSWERS. This allows you to ask questions about your data with natural language.

Just click Ask about this data to expand the Answer section.

Type the question Count Yes in column B and Press Enter. It gives you the Count Yes.

Count Yes with a Chart

A bar chart can present the data in two dimensions. The length of the bars indicates the size of the data, and the data is presented in an understandable and logical way.

In this section, you learn how to use a bar chart to Count Yes in Google Sheets.

Follow the below steps to create a bar char

1. Select your data range B1:B6.
2. Go to the Insert menu.
3. Select the Chart option.

You can see the Google Sheets added a pie chart and display a chart wizard on the right side of your Google Sheets.

4. Change the Chart type to Column Chart in the Setup tab.

5. Go to the Customize tab
6. Expand the Chart and Axis titles section.
7. Select the Chart title in the drop-down menu.
8. Update the Title text to Count of Yes.  

9. Select the Vertical axis title in the drop-down
10. Update the Title text to Count.

11. Expand the Series section.
12. Check the Data labels option.
13. Adjust the Data label font size.

You now have a bar chart that’s easy to read the current count of all the yes values in your data.

Count Yes with a Pivot Table

A Pivot Table is a powerful tool that allows you to summarize data in Google Sheets.

You can use a pivot table to create a new table that displays the summarized data. Once you have created a pivot table, you can use it to answer questions about the data.

You can use a pivot table to Count Yes in Google Sheets.

To create a pivot table, you first need to select the data that you want to summarize. Then, you can choose how you want to summarize the data by selecting the appropriate options in the pivot table editor.

To count how many yes in column B in your Google Sheets, you need to create a Pivot Table.  Follow the below steps to Count Yes with a Pivot Table in Google Sheets.

1. Select your data A1:B6.
2. Click on the Insert menu.
3. Select the Pivot Table option to open the Create pivot table dialog box.  

4. Choose the Existing sheet for the location of the new pivot table.
5. Select cell D1 or any empty cell in your sheet.
6. Press the Create button to add your pivot table.

You can see the empty pivot table is added to your Google Sheets, and you should also see the pivot table editor on the right side of your Google Sheets.

7. Press Add button present right to the Rows.
8. Choose the column with the yes values to count from your data. In this example, it’s the Done column from the list.

10. Press Add button present right to the Values.
11. Choose the same column from step 8.

You can see your pivot table summarizes your data with Count Yes values.

Conclusions

In this article, you learned many different ways to Count Yes in Google Sheets.

The SUBTOTAL function with filter toggle allows you to count either Yes or No values since it can count based on visible cells.  If you need to display both counts in your report you can use any of the other formula solutions.

Suppose you need to perform counts of yes values a lot. Then you can even create a custom function for this using Apps Scripts.

Column Stats or Explore features in Google Sheets helps you to view quick summaries of your data, and these summaries include counts based on the values in your data.

The pivot table tool can be used to create custom summaries of your data and this includes counts.

Do you know any other method to count Yes in Google Sheets? Let me know in the comments section!