5 Ways to Count Yes in Microsoft Excel

Counting Yes entries in Excel might seem simple at first glance, but if you’ve ever had to sift through hundreds of rows of data, you know how tedious it can get. It’s like constantly monitoring how many times a specific client agrees to terms in a long contract – you could do it manually, but why waste time when Excel can handle it in seconds?

In this Microsoft Excel tutorial, I’ll walk you through the quickest and the easiest ways to count Yes responses in worksheets. By the end, you’ll have a valuable Excel skill that saves you effort and reduces human error. Let’s get started!

Using the COUNTIF Function

The COUNTIF function counts cells that match a condition in a selected range. It works with numbers, text, and dates. This function is case-insensitive and supports wildcard characters for partial matches. This method is the most widely used to count a specific text or number in a range of cells because it has been there since Excel 2016.

Count Yes in Excel using the COUNTIF
Count Yes in Excel using the COUNTIF

Open your worksheet containing the source dataset and select a cell where you wish to count all Yes entries in the data.

Copy and paste the following formula into that cell and hit Enter:

=COUNTIF(B:B, "Yes")

You must edit the above formula to suit your dataset. For example, if the Yes values are in column F, change B:B to F:F.

Notice that I’ve not used the exact cell range, like B2:B11 in the above formula. That’s because I want to create a dynamic formula that’ll automatically count all the entries of a specific column, like column B, even when I keep adding new data below the cell B11. I don’t need to change the formula again when I update the source data column.

Congratulations, you’ve successfully counted the number of Yess in Excel using the COUNTIF function and the resultant value is in cell D2 or in whichever cell you have entered the above formula.

Using an Array Formula for Legacy Excel Editions

If you’re working on a dated Excel desktop app you can use an array formula made of the SUM and the IF functions.

Using SUM and IF to count Yes
Using SUM and IF to count Yes

Select a cell where you wish to calculate the total number of Yes entries in a column and type in the following formula:

=SUM(IF(B:B="Yes", 1, 0))  

Change the column reference, B:B, according to your dataset.

Creating an array formula
Creating an array formula

Now, press Ctrl + Shift + Enter to calculate the cell.

Excel will count all the Yess in the selected column.

Using Manual Filtering and Status Bar

Select cell and activate Sort & Filter
Select cell and activate Sort & Filter

Select the column header of the source dataset and press the Ctrl + Shift + L keys to activate Sort & Filter.

Select Yes in Filter
Select Yes in Filter

Click on the Filter drop-down menu on the column header where you’ve got the Yess to be counted.

Uncheck the Select All checkbox and checkmark the checkbox for Yes. Click OK to filter.

Filtered all Yess in a column
Filtered all Yess in a column

The Sort & Filter tool will filter out all unselected entries in the column except for the Yes values.

Count all Yess in Excel using Sort & FIlter
Count all Yess in Excel using Sort & Filter

Now, select the filtered column from the top to the bottom.

Find the total count of Yes in the Excel Status Bar. I’ve indicated the location in the screenshot given above.

Using a PivotTable

You can also use the Excel PivotTable tool to analyze your input dataset and calculate Yess. It’s particularly useful when your input dataset is really large. Also, if you wish to assess the numbers of Yess from multiple dimensions or column headers, PivotTable is the most suitable tool to choose. You can simply drag and drop columns over Values and Filters to quickly discover hidden insights.

If you’re wondering if PivotTable must be too technical to use, it’s totally the opposite. Follow the steps given below to learn the tricks you should use when using this powerful data summarizer command.

Select PivotTable command
Select PivotTable command

Select the input dataset and click on the PivotTable command in the Tables block of the Insert tab.

PivotTable from the table or range
PivotTable from the table or range

The PivotTable from the table or range dialog box will show up. Select the Existing worksheet option and click on a cell in the active worksheet where you wish to create the PivotTable.

PivotTable Fields
PivotTable Fields

Click OK to start analyzing your dataset in the Excel PivotTable tool.

Drag and drop Passed into Filters and Values
Drag and drop Passed into Filters and Values

Drag and drop the column that contains the Yes entries into the Filters and Values field. You’ll be able to do that on the PivotTable Fields navigation panel on the right side.

Apply a filter
Apply a filter

By default, the filter for the selected column, which is Passed in this tutorial will be set to All.

Click on the Filter drop-down menu and select Yes from the context menu.

You should now see the count if Yess in the Count of Passed cell below the filter.

Sample dataset 1

Now, let’s consider the value Yes or No in certain cells arrives from the input of another person or thing within the overall process. For example, in the above dataset, certain compliance checks have been carried out by specific inspectors. The relevant data is available under the Compliance Check and the Inspector columns.

Drag and drop Inspector into Rows
Drag and drop Inspector into Rows

You wish to find out how many Yess are there for the inspector named Scott.

Click on any of the PivotTable fields, like the Passed or Count of Passed cells in the worksheet to view the PivotTable Fields navigation panel. In your case, these column header names will change so keep that in mind if you don’t find columns exactly as mentioned in this tutorial.

Drag and drop the Inspector column into the Rows field.

You’ll now see a bifurcated count of Yess by all three inspectors. For Scott, it’s 2 counts of Yess.

Using the Power Query Editor

Power Query is a great choice for counting Yes entries when you have large or changing datasets. It helps automate the process, saving time and reducing errors. Unlike using formulas, Power Query makes it easy to clean and transform your data without changing the original.

To count the Yes values, just load your data into the Power Query Editor. Then, simple steps are applied to the group, and the Yes entries are aggregated. This method keeps things efficient and makes it easy to update your count as your data changes.

If you’re wondering if this method might demand Power Query M code scripting and database querying experiences, that’s quite the opposite. You’ll perform all the steps using visual interfaces without writing a single line of code yourself. Let me show you below.

From Database
From Database

You can import data into Power Query from two sources. One is an external data source from remote servers. For this, click on the Get Data command in the Data tab.

Hover the cursor over the primary source of data, like From Database.

Final data sources will show up in the overflow menu on the right. Choose your preferred source, like From Oracle or SQL Server Databases.

Follow onscreen instructions to load data from the above sources into the Power Query Editor interface.

From Table and Range
From Table and Range

Alternatively, you can send a dataset to be quantified for Yes entries from a local worksheet as well.

Select the source data and click on the From Table/Range command in the Get & Transform Data block of the Data tab.

Click OK on the Create Table dialog box to open the source dataset in Power Query.

Imported source data in Power Query
Imported source data in Power Query

Find above a sample dataset in the Power Query Editor interface.

Filter out No
Filter out No

Click on the filter drop-down arrow in the column header that contains the Yess. In this exercise, it’s the Passed column.

Uncheck everything in the context menu and only checkmark the checkbox for Yes.

Click OK to apply the filter rule.

Only Yess in the column
Only Yess in the column

The Passed column will only show the Yes values.

Statistics
Statistics

Click on the Passed column header and select the Statistics drop-down menu in the Transform tab.

Select the Count Values option in the Statistics context menu.

Count Yes in Excel using Power Query
Count Yes in Excel using Power Query

The Power Query Editor will show the Calculated Count window showing the total counts of Yess in the source data.

Close & Load To
Close & Load To

Now, click on the File tab and select the Close & Load To option.

Import Data dialog
Import Data dialog

The Import Data dialog box will show up.

Select the Existing worksheet option and select any cell on the active worksheet as the destination.

Imported count to Excel
Imported count to Excel

You’ll see the total count of Yess in a tiny table.

📚 Read more: If you liked this guide, you’ll also want to read and follow along with the methods outlined in the following tutorials. Do check these out!

Conclusions

From now on, if someone asks you how to count yes in Excel, you can quickly show them using any of the methods mentioned in this guide.

If this Excel tutorial helped you learn something new, leave a comment below to say cheers! Also, if you’ve found anything missing, don’t forget to share your feedback using the comment box.

About the Author

Bipasha Nath

Bipasha Nath

Bipasha is a technical content writer with 10+ years of experience in the technology industry. She previously worked in a SaaS software development company focused on SharePoint, OneDrive, Word, Excel, Word solutions.

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃