How to Count Yes and No in Excel [10 Ways]

ByJames Corrigan

Do you need to count Yes and No in Excel? You can choose any one of the methods to use to Count Yes and No, it will depend on the structure of your data, and there are multiple options available.

Reporting Yes &No, True & False, and Male & Female is one of the basic analyses in excel. Suppose you work with the survey data and need to report the status of completes. In the survey data file, you will have a field called status marked as Yes for complete otherwise No.

Frequently you have to report the number of complete and non-complete respondents to your stakeholders. 

This blog post explains to you various methods of how to count Yes and No in Excel.

Built-in functions such as COUNTIF, SUMPRODUCT, SUM & IF combination function in an array formula, and FILTER & COUNTA function together can all be used to count Yes and No in Excel.

Excel features Pivot Table, Analyze Data, Power Pivot, and Power Query will help to count Yes and No in Excel.If you need to count Yes and No in your automation report or dashboard, you can use either VBA or Office Scripts method.

This post explains step by step how to use each one of these methods with the below dataset.

Suppose you receive the sales data with four fields such as Emp ID, Emp Name, Actual, and Target.  You have to create a report on how many of the employees achieved the target.

Go to cell E1 and type the header Target Achieved. Enter the formula =IF( C2 >= D2, "Yes", "No" ) in cell E2 and then drag to fill in to apply cells E2:E16.

To create a report to display the number of employees who achieved the Target, you need to Count Yes and No.

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

Count Yes and No in Excel with COUNTIF function

COUNTIF is one of the statistical functions. It counts the number of cells that meet the criteria.

COUNTIF( range, criteria )

Type the formula =COUNTIF( E2:E16, "Yes" ) in cell E18 and =COUNTIF( E2:E16, "No" ) in cell E19.

Out of 15 employees, 6 of them are achieved the target.

Count Yes and No in Excel with SUMPRODUCT function

SUMPRODUCT is an advanced Excel function that sums based on specified criteria. It multiplies the range or array of numbers and returns the sum of the products. You learn how to apply this function to count Yes and No in Excel.

SUMPRODUCT( array1, array2, [array3],…[array_n] )

Go to cell E18, type the formula =SUMPRODUCT( ( E2:E16 = "Yes" ) * 1 ), and press Enter.

SUMPRODUCT function checks each value in the range of cells E2:E16, if the cell contains Yes, multiply by 1 and sum.

Type the formula =SUMPRODUCT( ( E2:E16 = "No" ) * 1 ), in cell E19 to count only No.

Count Yes and No in Excel with SUM and IF functions – Array Formula

The Excel array formula is a formula that can perform calculations on a range of data. You can use the array formulas to do complex calculations, which are not possible with regular formulas.  You have to press Ctrl + Shift + Enter whenever you need to enter an array formula in Excel.

 This section explains to you how to use the excel basic functions SUM and IF to count Yes and No.

Go to cell E18, type the formula =SUM( IF( E2:E16 = "Yes", 1, 0 ) ), and press Ctrl + Shift + Enter.

IF function checks each cell value in the range E2:E16 and return 1 or 0. And then the  SUM function sum all the ones and return the number.

Type the formula =SUM( IF( E2:E16 = "No" , 1 , 0 ) ), in cell E19 to Count only No and press Ctrl + Shift + Enter.

Count Yes and No in Excel with FILTER and COUNTA function

You can use the combination of functions or nested functions in Excel.  Learn how to use both FILTER and COUNTA functions together to count the number of Yes and No responses.

FILTER( array, include, [if_empty] )

The FILTER function filters a range of data based on criteria and returns an array.

COUNTA( value1, [value2], ...)

COUNTA returns the number of non-empty cells in a range.

Go to cell E18, type the formula =COUNTA( FILTER( $E$2:$E$16, $E$2:$E$16 = "Yes" ) ), and press  Enter.

FILTER function filters the range E2:E16 only the Yes value. And then COUNTA counts the number of items in that filtered range.

Type the formula =COUNTA( FILTER( $E$2:$E$16, $E$2:$E$16 = "No" ) ), in cell E19 to Count only No.

Count Yes and No in Excel with Pivot Table

Excel Pivot Tables allow you to summarize, analyze, sort, and filter data without any additional formula. It also helps you to identify trends and outliers.

Please follow the below steps to insert the Pivot Table in Excel

  1. Select the range of cells A1:E16 in the Pivot Tab
  2. Choose the PivotTable command in the Insert Menu
  3. Click From Table/Range option – to open the PivotTable from table or range dialog box.
  1. Select the option Existing Worksheet. you can select the New Worksheet option if you need the pivot in a new worksheet.
  2. Choose the cell A21, location to insert the pivot table.
  3. Press OK

Now you can see an empty pivot table is created in cell A21 in the Pivot Sheet.

  1. Drag the fields Target Achieved to Rows Pane and Emp ID to Values Pane.

Count Yes and No in Excel with Analyze Data

You can use AI- enabled excel feature the Analyze Data command in the Home Tab to count Yes and No. It allows you to analyze your data and create charts. Also, it can be used to identify KPIs quickly and easily.

Select the command Analyze Data in the Home Tab. Analyze Data Pane will appear on the right side of your application.  Type the question Count Yes and No Table for Target Achieved in the Ask a question about your data input box and then Press Enter.

Click the + Insert Button. The Analyze Data generates the Count Yes and No Table for the Target Achieved column in a new worksheet named Suggestion1.

Count Yes and No in Excel with Power Pivot

Excel Power Pivot is a great tool for data analysis and reporting. It allows you to load multiple data files and create relationships between them. Also, you can perform complex calculations and analyses using the DAX formula.

This approach same as the above Pivot table method, but it works when you work with multiple tables.

Select the range of cells A1:E16 and press the command Add to Data Model in the Power Pivot Tab.

In the Power Pivot editor, create a measure CountYes&No using the formula CountYes&No := counta( PPData[Target Achieved] ), and press Enter.

Alternatively, you can use the formula CountYesNo := CountRows( PPData )

Select the Flattened PivotTable option in the PivotTable command to insert the Pivot into the excel sheet.

Create Flattened PivotTable dialog box has options to select the worksheet and location to place the pivot table. Press the OK button.

Excel adds an empty pivot table in the worksheet, you drag the field Target Achieved in the Rows Area and the measure CountYes&No to the Values.

Count Yes and No in Excel with Power Query

Excel Power Query is a powerful Extract Transform Load ETL tool, which helps you to connect multiple data files, create relationships between them, transform, clean, and format the data.

Steps to Count Yes and No using Power Query

  1. Select the range of cells A1:E16 in the PowerQuery Sheet
  2. Choose From Table/Range option in Data Menu– to open the Power Query window.
  1. Rename your table PQData in the Name input box, and you can see two steps in the Applied steps.
  1. Select the Target Achieved column, Click the Right mouse button, and Choose the Remove Other Columns option to remove all other columns from your table.
  1. Select the Group By Command in Power Query Home Tab
  2. Since, the table has only one column Target Achieved  in your data, just press OK in the Group By dialog box
  1. Go to the Home Tab, Select the Close & Load command and Press Close & Load To…
  1. The above steps open the Import Data dialog box. In that, you can select the sheet and the location to insert the transformed Power Query table in your worksheet.

Count Yes and No in Excel with VBA

VBA code helps you to record repetitive tasks and automate processes. It can also be used to create custom functions and formulas, which can be used in formulas and calculations. Additionally, VBA can be used to create user interfaces, such as user forms and dialog boxes.

Sub countYesNo()
'Declare variables
Dim rge As Range
Dim cntYes  As Integer
Dim cntNo As Integer

'Assign values
Set rge = Sheets("VBA").Range("E2:E16")
cntYes = 0
cntNo = 0

'loop through each cell in the range
For Each rg In rge
    If rg.Value = "Yes" Then cntYes = cntYes + 1
    If rg.Value = "No" Then cntNo = cntNo + 1
Next

'Store count Yes and No
Sheets("VBA").Range("H2").Value = cntYes
Sheets("VBA").Range("H3").Value = cntNo

End Sub

Insert a module in VBE and add the above code

Macro declares the variables rge, cntYes and cntNo.  Assigns the range E2:E16 to the variable rge and initializes the variables cntYes and cntNo. Macro loop through each cell in the range of cells and add one to the respective variable cntYes or cntNo. Finally, the macro saves the cntYes and cntNo to the respective cells in your excel worksheet.

Steps to Run the CountYesNo macro.

  1. Select the range of cells E2:E16
  2. Press Alt + F8, the keyboard shortcut to open the Macro dialog box.
  3. Choose CountYesNo Macro and then Press Run.

Count Yes and No in Excel with Office Scripts

Office Scripts is a programming script that allows you to record repetitive task and automate it. You can record, edit, and run scripts to quickly and easily complete tasks.

function main(workbook: ExcelScript.Workbook) {

    //Assign values to variables
    let currSheet = workbook.getActiveWorksheet()
    let rng = currSheet.getRange("E2:E16");
    let rows = rng.getRowCount();

    //Initialize variable
    let cntYes = 0;
    let cntNo = 0;

    //loop through the range of cells
    for (let i = 0; i < rows; i++) {
      let cCellVal = rng.getCell(i, 0);

      //Check the condition and add 1 to cntYes
      if (cCellVal.getValue() == "Yes") {
        cntYes = cntYes + 1
      }

     //Check the condition and add 1 to cntNo
      if (cCellVal.getValue() == "No") {
        cntNo = cntNo + 1
      }

      //Save the count yes and no to the respective cells
      currSheet.getCell(1,7).setValue(cntYes)
      currSheet.getCell(2,7).setValue(cntNo)

    }

}

Office Scripts assign values to the variables currSheet, rng, and rows. Next, Initialize the variables cntYes and cntNo. Script loop through each cell in the range of cells E2:E16 and add one to the respective variable cntYes or cntNo. Finally, the script saves the cntYes and cntNo to the respective cells in the excel worksheet.

Follow the below steps to execute the CountYesNo Office script in your excel

  1. Select the range of cells E2:E16
  2. Go to Automate menu.
  3. Select the CountYesNo Script – to open the Code editor on the right side of the excel window
  4. Press Run 

Conclusions

You come across to count some specific text in a data field for your analysis.  For example. You may have data Yes and No, True and False, 1 and 0, Male and Female, etc., in a column and create the summary report of that column. Now you have learned few different methods to Count Yes and No values in excel.

You can use excel built-in functions COUNTIF, SUMPRODUCT, SUM & IF combination function in an array formula, and FILTER & COUNTA function together to count Yes and No.

You just type your question to create a count Yes and No table using Excel Analyze Data Feature.  Create a Pivot Table in Excel and drag & drop the fields to the respective areas to count Yes and No.  If you are working with multiple data sources or a large dataset, use Power Query and Power Pivot methods. VBA or Office Scripts helps to count Yes and No in your automation or dashboard.

What is your preferred method to Count Yes and No in Excel? Let me know in the comments!

Similar Posts