How to Sum Yes and No in Excel: 5 Secret Methods

Are you tired of sifting through your Excel worksheets to manually count the number of “Yes” and “No” values? Do you find yourself spending hours trying to analyze your data and make informed decisions? Then you must read this Excel tutorial to learn how to sum “Yes” and “No” in Excel.

It’s quite common to get data from third-party sources or databases in undesirable formats. For example, you get a lot of yesses and nos when you run online polls or surveys. However, Microsoft Excel has a lot of built-in tools and functions that you can use to convert these text values into numbers that make a better sense of polls.

Keep reading to find formulas, codes, and automation to translate thousands of yesses and nos in an Excel worksheet into integers for quick and effective data visualization.

Reasons to Learn This Excel Skill

• Excel can add up yesses and nos in less than a second.
• You may miss a few yesses and nos when you count these manually but Excel won’t.
• Knowing how to sum “Yes” and “No” values in Excel is a simple skill that many employers value.

Also read: How To Convert True/False To Yes/No In Excel

1. Using IF and SUM Formulas

This is the easiest of all and should be available in all the latest and dated versions of Excel desktop apps, and Excel for Mac apps, including the web version of Excel. Here’s how you can put this method into good use:

1. List down the items in the first column (Column A). Then, copy and paste the respective “Yes” and “No” tags to the next column (Column B).

2. If you already have the Excel data adjusted in the above format, create the Yesses and Nos columns.

3. Now, for the Yesses column, copy and paste this formula:

=IF(B2="Yes",1,0)

4. For the Nos column, use this formula:

=IF(B2="No",1,0)

5. As soon as you paste the above formulas in the respective cells, you’ll see values in 1s and 0s corresponding to Yes and No.
6. Apply the formula across the cells under the Yesses and Nos column headers.

7. Now, apply the SUM formula at the bottom of the Yesses and Nos column to add up all “Yes” and “No” values. For example, here are the formulas you must use:

=SUM(C2:C21)

=SUM(E2:E21)

8. So, now you’ve got a total count of Yes and No in your Excel worksheet.

2. Using COUNTIF Formula

COUNTIF is a function in Microsoft Excel that allows you to count the number of cells in a given range that meet a specified condition. It takes two arguments. The first argument is the range of cells you want to count. And, the second argument is the condition the cell ranges must meet so Excel can count those. Find below how to use this powerful function to add up “Yes” and “No” in Excel:

Add Yesses

1. Select a cell where you want to count the total instances of yesses in a survey or poll data.
2. Then, copy and paste the following formula inside the selected cell:

=COUNTIF(B2:B21,"Yes")

3. Don’t forget to adjust the cell range B2:B21 to an appropriate cell range where you’ve got the “Yes” and “No” tags for the surveyed object.
4. In the current example, I surveyed the usability of Windows 11 and Windows 10 for gaming purposes. Windows OS (Column A) consists of the survey objects, which are Win 11 and Win 10. In the next column, For Gaming (Yes/No), which is Column B, I imported the “Yes” and “No” tags from an online survey tool. So, the “Yes” and “No” values are within the cell range B2:B21.
5. Hit Enter and Excel will count the total “Yes” in the first cell under the Count of Yes column.

Add Nos

1. Highlight another cell where you want Excel to sum all “No” tags from the survey.
2. This time, copy the above formula with a small change as shown below:

=COUNTIF(B2:B21,"No")

3. Again, you should replace B2:B21 with an appropriate cell range.
4. Hit Enter and you have the total count of “No” tags in the designated cell.

3. Using a PivotTable

Using the above formulas requires additional editing of the Excel worksheet so the audience can understand the data you’re presenting. To avoid excessive editing of the Excel worksheet with texts and legends, you can use a PivotTable to sum ‘Yes” and “No” in Excel. Here’s how it’d done:

Preparing Data

1. First off, arrange your Excel table or survey data in the format shown in the above image.
2. Now, add Yesses and Nos columns to the next columns of the existing data, as shown in columns C and D.
3. Then, apply this formula in all the cells under the Yesses column:

=IF(B2="Yes",1,0)

4. Similarly, populate 1s and 0s for the Nos column by applying this formula across all the cells:

=IF(B2="No",1,0)

4. Now, your worksheet will look something like the above image.

Creating a PivotTable

1. Highlight the entire data you created earlier.
2. Click the Insert tab on the Excel ribbon and then select the PivotTable drop-down list in the Table block.
3. In the drop-down list, click on From Table/Range.
4. A pop-up dialog box will show up. Simply hit OK to open the PivotTable editor in a new worksheet tab.

5. In the PivotTable Fields navigation panel on the right side, do the following:
   • Drag Windows OS and For Gaming (Yes/No) into the Rows box
   • Then, drag and drop the Yesses and Nos columns into the Values section
6. Below column A, you should have already discovered an itemized description of Yes and No sums at the survey subject level and overall.

7. For example, Win 10 and Win 11 respectively have 8 and 2 yesses. On the other hand, there are 2 nos for Win 10 and 6 nos for Win 11. In total, the survey earned 12 yesses and 8 nos.

4. Using a VBA Macro

There’s also this tiny VBA script that you can add to the Excel worksheet and call the macro anytime to add up all yesses and nos within a designated cell range. Here’s the VBA code and steps to execute the code in Excel:

1. Save your Excel workbook as a Macro-Enabled file by clicking the File tab, Save As, and choosing a location on the local storage or OneDrive. Then, click the Save as type drop-down menu on the Save As dialog box and select Excel Macro-Enabled Workbook. Hit the Save button to complete the process.
2. Now, hit Alt + F11 keys together and create a new module by clicking Insert > Module in the VBA Editor tool.

3. Copy and paste the following script inside the new Module:

Sub SumYesNo()
    Dim YesCount As Long
    Dim NoCount As Long
    
    'Loop through each cell in the range B2:B21
    For Each cell In Range("B2:B21")
        'Check if the cell value is "Yes"
        If cell.Value = "Yes" Then
            'Increment the Yes count
            YesCount = YesCount + 1
        'Check if the cell value is "No"
        ElseIf cell.Value = "No" Then
            'Increment the No count
            NoCount = NoCount + 1
        End If
    Next cell
    
    'Display the Yes and No counts in separate cells
    Range("D1").Value = "Yes Count:"
    Range("D2").Value = YesCount
    Range("E1").Value = "No Count:"
    Range("E2").Value = NoCount
End Sub

4. Click the Save button on the VBA Editor and close it.

5. Now, press the Alt + F8 keys together to open the Macro dialog box.
6. There, select the SumYesNo macro and hit the Run button.
7. You should find the sum of “Yes” and “No” in D2 and E2 cells.

You’ll need to modify the above VBA script in the following code elements:

1. Range("B2:B21"): to declare the position of “Yes” and “No” values
2. Range("D1"): where you need Yes Count text
3. Range("D2"): where you want the sum of yesses
4. Range("E1"): cell address to get the No Count text
5. Range("E2"): where you want the sum of nos

5. Using Office Scripts in Excel

You can’t use the above VBA script in the web version of Excel. Then, how can you automate the process when working on Excel for the web? Here comes Excel Automate or Office Scripts. If you own a Microsoft 365 subscription, then you can use Office Scripts to automate Excel tasks on the web app. This feature is also available on the latest Excel 365 desktop app.

Let’s find out which script you should use and how to apply that in an Excel worksheet:

1. On your Excel worksheet that contains the “Yes” and “No” data, click Automate and then hit the New Script button inside the Scripting Tools block.

2. Then, copy and paste the following script inside the Code Editor on the right-side navigation pane:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range F2:F3 on selectedSheet
	selectedSheet.getRange("F2:F3").setValues([["Count of Yes"],["Count of No"]]);
	// Auto fit the columns of range F:F on selectedSheet
	selectedSheet.getRange("F:F").getFormat().autofitColumns();
	// Set range G2:G3 on selectedSheet
	selectedSheet.getRange("G2:G3").setFormulasLocal([["=COUNTIF(B2:B21,\"Yes\")"],["=COUNTIF(B2:B21,\"No\")"]]);
}

3. Before saving and running the script, you must make the following changes to the script:
   • Change all the occurrences of cell range F:F and F2:F3 in the code to another cell range where you want to populate the count of yesses and nos.
   • Similarly, you also need to change G2:G3 to another suitable cell range according to your worksheet data.
   • Finally, don’t forget to change the actual “Yes” and “No” tag cell range which is B2:B21.
4. Now, hit the Save script button below the Script details section and then click the Run button.

5. In less than a second, you should get a sum of all yesses and nos in the designated cell range in separate cells. To be precise, to the right of cells Count of Yes and Count of No.

Conclusion

So, now you know almost all the popular and proven methods to calculate and sum “Yes” and “No” in Excel. The IF and COUNTIF formulas are good if you’re an entry-level Excel user.

Contrarily, if you’ve got some experience working with Excel and know about tables well, then you might want to check out the PivotTable-based method to add up yesses and nos.

Finally, if you’re an expert in Excel and like codes over simple formulas, then you’ll find the VBA macro and Office Scripts-based method more useful.

Give any of the methods a try and share your experience in the comment box. Also, share this useful Excel tutorial with your friends and colleagues so they can also learn how to sum “Yes” and “No” in Excel.