How to Calculate Yes Percentage from Selected Cells in WPS Spreadsheets
Question details
The user needs to calculate the percentage of Yes responses from selected non-adjacent cells. The result divides the number of Yes values by the total number of Yes and No values and ignores other cell contents.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has response data in selected cells across a sheet or workbook and wants a single percentage for Yes answers without manually counting them.
- Observed behavior
- The desired result is a percentage value that counts only Yes and No responses from the specified cells and excludes blanks or other labels.
Confirm that the cells you want to include contain the response labels exactly as "Yes" and "No" without extra spaces, and that all referenced cells are in the same workbook.
Calculate Yes percentage with COUNTIF for selected cells
Use this method when Yes/No responses are in non-adjacent cells and you need a result that ignores blanks and other text.
The formula adds individual COUNTIF results for each selected cell. Because the denominator counts only Yes and No, other values and blank cells do not affect the percentage.
This approach works in WPS Spreadsheets and Microsoft Excel without requiring dynamic array functions.
In WPS Office, open the spreadsheet that contains the Yes/No cells you want to calculate.
Select an empty cell, such as Sheet2!D1, where the percentage will be displayed.
Type = (COUNTIF(Sheet2!A1,"Yes")+COUNTIF(Sheet2!A3,"Yes")+COUNTIF(Sheet2!A4,"Yes")+COUNTIF(Sheet2!B1,"Yes")+COUNTIF(Sheet2!B4,"Yes")+COUNTIF(Sheet2!C1,"Yes")+COUNTIF(Sheet2!C3,"Yes"))/((COUNTIF(Sheet2!A1,"Yes")+COUNTIF(Sheet2!A3,"Yes")+COUNTIF(Sheet2!A4,"Yes")+COUNTIF(Sheet2!B1,"Yes")+COUNTIF(Sheet2!B4,"Yes")+COUNTIF(Sheet2!C1,"Yes")+COUNTIF(Sheet2!C3,"Yes"))+(COUNTIF(Sheet2!A1,"No")+COUNTIF(Sheet2!A3,"No")+COUNTIF(Sheet2!A4,"No")+COUNTIF(Sheet2!B1,"No")+COUNTIF(Sheet2!B4,"No")+COUNTIF(Sheet2!C1,"No")+COUNTIF(Sheet2!C3,"No"))) and press Enter.
On the Home tab, click Number Format and choose Percentage, or press Ctrl+Shift+%.
Confirm that the result equals the Yes count divided by the Yes plus No count. Cells with other values are excluded.
Use LET and VSTACK for Microsoft 365 or compatible WPS versions
Use this shorter dynamic-array formula if your spreadsheet application supports LET and VSTACK.
Calculate Yes Percentage from Selected Cells in WPS Spreadsheets
WPS Spreadsheets can calculate the Yes percentage by summing COUNTIF results for each selected cell and dividing by the total Yes and No responses. This approach ignores other values and works without requiring dynamic array functions.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the workbook that contains the Yes/No response cells.
- 2. Select the result cell: Click an empty cell where the percentage should appear, such as Sheet2!D1.
- 3. Enter the COUNTIF formula: Type the formula that sums COUNTIF results for each selected Yes/No cell and divides by the Yes plus No total, then press Enter.
- 4. Apply percentage formatting: On the Home tab, open the Number Format drop-down and choose Percentage, or press Ctrl+Shift+%.
- 5. Verify the result: Confirm that the displayed percentage reflects only Yes and No responses from the selected cells and ignores other values.
Frequently Asked Questions
Can I use this formula when the Yes/No cells are on different sheets?
Yes. Reference each sheet and cell directly in the COUNTIF parts, such as Sheet2!A1 and Sheet3!B2. If your version supports LET and VSTACK, you can stack those references in one range.
Why does the formula ignore blank cells and other text?
The denominator is built from COUNTIF criteria for "Yes" plus COUNTIF criteria for "No" only. Blank cells and values such as "Maybe" are not included in either count.
How do I display the result as a percentage instead of a decimal?
Select the result cell, go to the Home tab, open the Number Format drop-down, and choose Percentage. You can also press Ctrl+Shift+%.
Can I replace "Yes" and "No" with other labels such as "Y" and "N"?
Yes. Change the criteria in every COUNTIF part from "Yes" to "Y" and from "No" to "N", keeping the same formula structure.
