How to Check if All Cells Contain Yes in WPS Spreadsheets
Question details
The user needs a formula in D4 that returns yes only when every cell in H5:H10 contains yes, and returns no when any cell contains no or another value.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a worksheet and wants to verify that all six cells in the range H5:H10 meet a specific yes condition.
- Observed behavior
- The goal state is that D4 shows yes only when all six cells in H5:H10 contain yes; otherwise D4 shows no.
Confirm that the target range is exactly H5:H10 and that D4 is empty or ready to receive the formula. Decide whether uppercase and lowercase 'yes' should be treated the same, because the COUNTIF approach is case-insensitive.
Use COUNTIF to Test All Cells in H5:H10 for Yes
Best for most users because it works in WPS Spreadsheets and Excel versions and returns exactly the required yes/no result.
The COUNTIF function counts how many cells in H5:H10 match the text "yes". Wrapping it in IF checks whether that count equals 6, the number of cells in the range, and returns "yes" only when every cell qualifies.
Click cell D4 in your worksheet to make it the active cell where the result will appear.
Type =IF(COUNTIF(H5:H10,"yes")=6,"yes","no") in the formula bar and press Enter.
Check D4. It displays yes when all six cells in H5:H10 contain yes; otherwise it displays no.
Use an AND Array Formula to Evaluate Each Cell Individually
Use this if you prefer a logical per-cell comparison and your spreadsheet version supports array evaluation.
Test Multiple Cells with One Yes/No Formula in WPS Spreadsheets
Open your workbook in WPS Spreadsheets, select D4, and enter =IF(COUNTIF(H5:H10,"yes")=6,"yes","no") to get an instant yes/no result. WPS Spreadsheets supports Excel formulas and file formats, so the same COUNTIF logic works without changing your workflow.
- 1. Open the workbook: Launch WPS Office, click Open, and select the workbook that contains the H5:H10 range.
- 2. Select cell D4: Click cell D4 where the yes/no result should appear.
- 3. Type the formula: Enter =IF(COUNTIF(H5:H10,"yes")=6,"yes","no") in the formula bar.
- 4. Press Enter and review: Press Enter. D4 now shows yes only when every cell in H5:H10 contains yes; otherwise it shows no.
Frequently Asked Questions
Does COUNTIF distinguish between uppercase YES and lowercase yes?
No. COUNTIF is case-insensitive in WPS Spreadsheets, so uppercase YES and lowercase yes both count as a match. If you need strict case matching, use an EXACT-based array formula instead.
Can I use this formula for a different range?
Yes. Replace H5:H10 with your actual range and change 6 to the number of cells in that range. For example, a range of A1:A10 would use =IF(COUNTIF(A1:A10,"yes")=10,"yes","no").
What should I do if the formula returns an error?
Check that the formula starts with an equal sign, includes quotation marks around yes, and references exactly the range H5:H10. Also confirm that D4 is selected as the output cell.
Does WPS Spreadsheets support Excel COUNTIF formulas?
Yes. WPS Spreadsheets supports COUNTIF, IF, and AND functions, and it can open and save .xlsx Excel workbooks while keeping formula behavior compatible.
