WPS Office Excel: Return Good or Bad When All Matching Values Meet a Condition
Question details
The user needs an Excel formula that checks all rows matching a lookup value and returns Good only when every matching row has A in the corresponding status column; otherwise, it returns Bad.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- A data set contains lookup values in column A, matching identifiers in column B, and status values in column C. The user wants to flag each lookup row as Good or Bad based on all associated status values.
- Observed behavior
- The desired result is Good when every matching row has A in column C, and Bad when any matching row contains a different value.
Arrange your data with lookup values in column A, matching values in column B, and status values in column C. If your data extends beyond row 10,000, update the COUNTIFS range before entering the formula.
Use COUNTIFS to Return Good or Bad When All Matching Rows Meet the Condition
Use this solution when you need to mark lookup values as Good only if COUNTIFS finds no matching row with a status other than A.
The formula uses COUNTIFS to count rows in column B that equal the lookup value in column A and whose column C status is not A. If the count is zero, every matching row has A, so the formula returns Good.
Click the cell where you want the Good/Bad result to appear, for example D1.
Type =IF(A1="","",IF(COUNTIFS($B$1:$B$10000,A1,$C$1:$C$10000,"<>A")=0,"Good","Bad")) and press Enter.
Drag the fill handle down through the remaining lookup values in column A to apply the formula to all rows.
Create a Good/Bad Status Formula with COUNTIFS in WPS Office Excel
Use WPS Office Excel to apply the COUNTIFS-based Good/Bad formula across your matching data and get reliable condition checks without extra tools.
- 1. Open your workbook in WPS Office Excel: Launch WPS Office Excel and open the workbook that contains columns A, B, and C.
- 2. Select the result cell and enter the formula: Click in D1 and type =IF(A1="","",IF(COUNTIFS($B$1:$B$10000,A1,$C$1:$C$10000,"<>A")=0,"Good","Bad")).
- 3. Apply the formula to all lookup rows: Press Enter, then drag the fill handle from D1 down through all values in column A.
- 4. Verify the Good/Bad results: Confirm that Good appears only when all matching rows in column C are A and Bad appears for any failed match.
Frequently Asked Questions
Why does the formula use COUNTIFS with "<>A"?
It counts matching rows where column C is not A. If that count is zero, all matching rows have A and the formula returns Good.
How do I change the condition from A to another value?
Replace "<>A" with the desired not-equal condition, for example "<>B", or adjust the column references to match your data.
Can I use this formula in WPS Office Excel and Microsoft Excel?
Yes, COUNTIFS works the same way in WPS Office Excel and Microsoft Excel, so the formula can be used in both.
What should I do if my data extends beyond row 10000?
Change the ranges $B$1:$B$10000 and $C$1:$C$10000 to cover your actual data range, for example $B$1:$B$50000.
Why does the formula return blank when the lookup cell is empty?
The outer IF checks whether A1 is blank and returns an empty string to avoid marking empty lookup rows.
