# 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:

- 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**).

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

- Now, for the
**Yesses**column, copy and paste this formula:

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

- For the
**Nos**column, use this formula:

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

- 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**. - Apply the formula across the cells under the
**Yesses**and**Nos**column headers.

- 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)`

- 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

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

`=COUNTIF(B2:B21,"Yes")`

- 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. - 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`. - Hit
`Enter`and Excel will count the total “**Yes**” in the first cell under the**Count of Yes**column.

### Add Nos

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

`=COUNTIF(B2:B21,"No")`

- Again, you should replace
`B2:B21`

with an appropriate cell range. - 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

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

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

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

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

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

### Creating a PivotTable

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

- 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

- Drag
- Below
**column A**, you should have already discovered an itemized description of Yes and No sums at the survey subject level and overall.

- 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:

- 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. - Now, hit
`Alt`+`F11`keys together and create a new module by clicking**Insert**>**Module**in the**VBA Editor**tool.

- 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
```

- Click the
**Save**button on the**VBA Editor**and close it.

- Now, press the
`Alt`+`F8`keys together to open the**Macro**dialog box. - There, select the
**SumYesNo**macro and hit the**Run**button. - 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:

`Range("B2:B21")`

: to declare the position of “**Yes**” and “**No**” values`Range("D1")`

: where you need**Yes Count**text`Range("D2")`

: where you want the**sum of yesses**`Range("E1")`

: cell address to get the**No Count**text`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:

- 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.

- 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\")"]]);
}
```

- 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`

.

- Change all the occurrences of cell range
- Now, hit the
**Save script**button below the**Script details**section and then click the**Run**button.

- 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.