WPS Spreadsheets Formula to Check Multiple Cells for Allowed Values
Question details
The user needs a WPS Spreadsheets formula that returns Yes only when all cells in AB1:AF1 contain either a-successful or b-partial successful, and returns No for any other value.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is validating a row of five status cells in a worksheet and wants one result cell to confirm that every status is approved.
- Observed behavior
- The goal state is a single Yes/No result that indicates whether AB1:AF1 all match one of the two allowed text values.
Verify that AB1:AF1 contains exactly five cells and that the status text matches a-successful or b-partial successful with no extra spaces, because the COUNTIF formula uses exact text matching.
Use COUNTIF to Check All Five Cells for Allowed Values
Use this formula when you need one cell to return Yes only if every cell in AB1:AF1 contains a-successful or b-partial successful.
The COUNTIF function counts cells in AB1:AF1 that match either allowed value. Comparing the total to 5 ensures all five cells qualify before returning Yes.
Click the cell where you want the Yes/No result to appear, for example AG1.
Type =IF(SUM(COUNTIF(AB1:AF1,{"a-successful","b-partial successful"}))=5,"Yes","No") and press Enter.
Change one cell in AB1:AF1 to another value, such as failed, and confirm the formula returns No.
Use COUNTIFS as a Shorter Alternative
Use this alternative if you only need a TRUE/FALSE result and prefer a formula without the IF wrapper.
Check Allowed Values in WPS Spreadsheets with COUNTIF
WPS Spreadsheets supports the same COUNTIF, COUNTIFS, and IF functions as Excel, so you can paste the formula directly into your workbook to validate AB1:AF1 against the two allowed statuses.
- 1. Open WPS Spreadsheets: Download and install WPS Office, then open WPS Spreadsheets from the home screen.
- 2. Open your workbook: Click Menu > Open, select the workbook that contains AB1:AF1, and go to the worksheet where the status cells are located.
- 3. Enter the validation formula: Select the result cell and type =IF(SUM(COUNTIF(AB1:AF1,{"a-successful","b-partial successful"}))=5,"Yes","No"), then press Enter.
- 4. Save the workbook: Click Menu > Save As and choose Excel Workbook (.xlsx) format if you need to share the file with Microsoft Excel users.
Frequently Asked Questions
What does the formula =IF(SUM(COUNTIF(AB1:AF1,{"a-successful","b-partial successful"}))=5,"Yes","No") do?
It counts how many cells in AB1:AF1 contain either a-successful or b-partial successful, then returns Yes only when the count equals 5. Otherwise it returns No.
Can I change the range AB1:AF1 to another range?
Yes. Replace AB1:AF1 with your target range in both the COUNTIF formula and the =5 comparison. The =5 must match the number of cells in the range.
Does this formula work in Microsoft Excel too?
Yes. WPS Spreadsheets and Microsoft Excel support COUNTIF, COUNTIFS, and IF with array constants, so the formula can be used in either application.
What if my cells contain extra spaces around a-successful or b-partial successful?
The formula uses exact text matching, so extra leading or trailing spaces will cause cells to be counted as invalid. Remove the spaces or use TRIM to clean the values first.
How can I return TRUE or FALSE instead of Yes or No?
Use =SUM(COUNTIFS(AB1:AF1,{"a-successful","b-partial successful"}))=5. It returns TRUE when all five cells are valid and FALSE otherwise.
