Formula Errors

How to Show Whether All Excel Cells Pass or Fail Testing

Natalie Taylor Oct 23, 2026 868 views

Question details

The user wants to create a single summary cell that evaluates an entire data range to display whether all cells pass testing, all cells fail testing, or if there is a mixture of both.

How to Show Whether All Excel Cells Pass or Fail Testing in Excel

Product: Excel
Device & OS: not provided
Scenario: Summarizing test results in a standard cell range without formatting the data as a formal table.
Observed behavior: A single summary cell dynamically and accurately outputs 'all cells pass testing', 'all cells fail testing', or 'some pass and some fail' based on the text evaluated in the target range.

Before you start

Ensure that the text in your target range exactly matches the criteria words (such as 'passes testing' or 'fails testing') without extra leading or trailing spaces, as formulas are sensitive to exact matches.

Solution 1Recommended

Use a Nested IF, COUNTIF, and COUNTA Formula

This method counts the occurrences of specific text strings in a range and compares that count to the total number of non-empty cells to determine the final summary status.

The COUNTIF function counts how many times a specific phrase appears, while the COUNTA function counts all non-blank cells in the range. By comparing these two values within a nested IF statement, you can accurately evaluate whether the entire range shares the same test result.

Select the summary cell

Click on the single cell where you want the final summary result (e.g., 'all cells pass testing') to appear.

Enter the nested formula

Type the following formula into the formula bar: =IF(COUNTIF(A2:B14,"passes testing")=COUNTA(A2:B14),"all cells pass testing",IF(COUNTIF(A2:B14,"fails testing")=COUNTA(A2:B14),"all cells fail testing","some pass and some fail")). Make sure to replace 'A2:B14' with your actual data range.

Apply and test the formula

Press Enter to apply the formula. The summary cell will instantly calculate the result and will automatically update if you change the test results in the target range.

Use a Nested IF, COUNTIF, and COUNTA Formula

Adjusting Criteria Text: Make sure to change the exact phrases 'passes testing' and 'fails testing' in the formula string to perfectly match the terminology used in your specific worksheet.

Analyze Data Efficiently

Easily Evaluate Test Results with WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical functions like IF, COUNTIF, and COUNTA, allowing you to easily summarize test results, track performance, and analyze your data seamlessly.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your test results.
2. Input the summary formula: Select your target summary cell and enter the nested IF and COUNTIF formula as demonstrated in the solution.
3. Calculate and update: Press Enter to calculate the summary, which will dynamically refresh as your data updates.

100% compatible with Microsoft Excel formulas, functions, and file formatsProcess complex nested logical formulas accurately and instantlyLightweight software design that runs smoothly even on older devicesFree and intuitive tabbed interface for easier multi-workbook management

Frequently Asked Questions

What if my cells contain extra spaces around the text?

If your cells have leading or trailing spaces, the COUNTIF function might not recognize the text correctly. You can wrap your evaluation range in the TRIM function, or use wildcard characters like "*passes testing*" within your COUNTIF criteria to capture the text.

Can I ignore completely blank cells in my summary formula?

Yes, the COUNTA function automatically ignores completely blank cells. Just ensure that the cells you want evaluated actually contain text, as COUNTA will evaluate any cell that is not totally empty (including cells with hidden spaces).

How can I apply color formatting based on the summary result?

You can use Conditional Formatting. Select the summary cell, go to the Home tab, click 'Conditional Formatting', and choose 'Highlight Cells Rules'. Use the 'Text that Contains' rule to format 'all cells pass testing' in green and 'all cells fail testing' in red.

Excel & Data Problems