How to Use IF to Convert Yes or No Text to Values in WPS Office Spreadsheets
Question details
The user needs to convert the text response Yes in cells B2:B4 to 1 and all other responses, including No and Do not know, to 0, then total the results.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is analyzing survey-style responses in cells B2:B4 and needs a numeric value for each response so the total number of Yes responses can be calculated.
- Observed behavior
- The cells contain exact text responses such as Yes, No, and Do not know; the goal is a formula-based total of Yes responses without changing the original text.
Open the workbook in WPS Spreadsheets and click the sheet that contains the B2:B4 responses. Select a blank result cell, and confirm that the values are entered as text so the formula comparisons work as expected.
Total all Yes responses with SUMPRODUCT
Use this one-formula method when you want a single total for Yes responses in B2:B4 without adding helper columns.
SUMPRODUCT with -- converts each comparison result to 1 for an exact Yes match and 0 for any other response, then totals the converted values.
Click a blank cell, such as D2, where you want the total number of Yes responses to appear.
Type =SUMPRODUCT(--(B2:B4="Yes")) into the selected cell.
Press Enter to calculate the total. WPS Spreadsheets returns the count of cells in B2:B4 that contain exactly Yes, while No and Do not know responses return 0.
Convert each response to 1 or 0 with IF in a helper column
Use this method when you also need to see the 1 or 0 value for each individual row before totaling.
Use WPS Spreadsheets to Convert Yes or No Text to 1 and 0
WPS Spreadsheets supports the same Excel-compatible IF and SUMPRODUCT formulas, so you can convert survey text responses to numeric values and total them without changing your original data.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, then open the workbook that contains the Yes, No, or Do not know responses in cells B2:B4.
- 2. Select the result cell: Click a blank cell, such as D2, where the total number of Yes responses should appear.
- 3. Enter the formula: Type =SUMPRODUCT(--(B2:B4="Yes")) and press Enter to get the total Yes count.
- 4. Save the workbook: Click Menu > Save or press Ctrl+S to keep the formula result. WPS preserves Excel file formats when saving.
Frequently Asked Questions
What formula changes Yes to 1 and No or Do not know to 0 in a single cell?
Use =IF(B2="Yes",1,0). This returns 1 only when B2 exactly contains Yes; any other text, including No and Do not know, returns 0.
How do I total all Yes responses in B2:B4 without a helper column?
Use =SUMPRODUCT(--(B2:B4="Yes")). This evaluates the exact Yes condition for each cell, converts TRUE and FALSE to 1 and 0, and totals the results.
Is the Yes comparison case-sensitive in WPS Spreadsheets?
No. Normal text comparison in WPS Spreadsheets is case-insensitive, so yes and YES also match. For a case-sensitive check, use =IF(EXACT(B2,"Yes"),1,0) in a helper column.
Can I use COUNTIF instead of SUMPRODUCT for this total?
Yes. =COUNTIF(B2:B4,"Yes") directly counts the Yes responses in B2:B4. However, it does not show the intermediate 1/0 conversion that IF or SUMPRODUCT provides.
What if my Yes responses have leading or trailing spaces?
Extra spaces prevent an exact match. Use =IF(TRIM(B2)="Yes",1,0) in a helper column, or clean the data with TRIM first, then use the total formula.
