WPS Spreadsheet: Count Yes and No Answers by Question with a PivotTable
Question details
The user needs to count Yes and No responses separately for multiple questions.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The response data contains question identifiers and Yes/No answers, and the user wants a summary for each question.
- Observed behavior
- The Yes and No responses are not yet summarized by question.
Arrange the source data in a normalized list with one row per person and question. Include fields such as Name, Question, and Answer, and use consistent values such as 1 for Yes and 0 for No.
Create a PivotTable that counts Yes and No responses by question
Use this method when you want a quick summary without adding formulas to the worksheet.
A PivotTable can group each question into rows, separate the Answer values into columns, and count each response automatically. The source list should have one record per person and question.
Click any cell in the normalized data list, or select the full range containing the Question and Answer columns.
Open the Insert tab and choose PivotTable. Confirm the source range and select a destination worksheet when prompted.
In the PivotTable Fields pane, drag the Question field to the Rows area so each question appears as a separate row.
Drag the Answer field to the Columns area to create separate Yes and No columns.
Drag Answer to the Values area, open its value settings, and choose Count if WPS Spreadsheet does not select Count automatically.
Count Yes and No responses with COUNTIFS formulas
Use formulas when you need reusable counts in a report or dashboard.
Total Yes indicators with SUMIFS
Use SUMIFS when Yes is represented by 1 and No is represented by 0 and you want the Yes total for each question.
Summarize Yes and No responses in WPS Spreadsheet
WPS Spreadsheet can organize normalized survey data into a PivotTable or calculate response totals with COUNTIFS and SUMIFS. Its familiar spreadsheet tools make it easy to build question-by-question summaries and share them in Excel-compatible workbooks.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the response list.
- 2. Normalize the data: Make sure each row represents one person and question, with Question and Answer fields in separate columns.
- 3. Build the summary: Use Insert > PivotTable, or enter COUNTIFS and SUMIFS formulas in a new worksheet.
- 4. Save the results: Choose File > Save As and select XLSX if the summary needs to be shared with Microsoft Excel users.
Frequently Asked Questions
How should the source data be arranged for the PivotTable?
Use one row for each person-question response. Keep fields such as Name, Question, and Answer in separate columns, with a single header row and no merged cells.
How do I count text values such as Yes and No instead of 1 and 0?
Place the text criteria directly in COUNTIFS, such as =COUNTIFS($B$2:$B$113,3,$C$2:$C$113,"Yes") and =COUNTIFS($B$2:$B$113,3,$C$2:$C$113,"No").
Why does my PivotTable show Sum instead of Count?
In the PivotTable Fields pane, open the field settings for Answer in the Values area and change the calculation from Sum to Count. This is especially important when the answers are stored as 1 and 0.
Can I summarize every question without changing the formula manually?
Yes. Put each question number in a separate result row, reference that cell in the COUNTIFS or SUMIFS criterion, and copy the formula down. A PivotTable also groups all question numbers automatically.
Can WPS Spreadsheet save this summary for Excel users?
Yes. Use File > Save As and choose the XLSX format. PivotTables and standard COUNTIFS or SUMIFS formulas can be shared in Microsoft Excel-compatible workbooks.
