Formula Errors

How to Use Excel Formula to Return PASS When Both Subjects Are Passed

Kushani Nimanthika Oct 9, 2026 869 views

Question details

The user needs an Excel formula to evaluate multiple attempt cells across two subjects, returning PASS only if both subjects have at least one PASS recorded. Otherwise, it should return FAIL.

How to Write an Excel Formula to Return PASS When Both Subjects Are Passed

Product: Excel
Device & OS: not provided
Scenario: Evaluating student or candidate test scores where a passing grade requires passing both a Reading and a Writing component, allowing for multiple attempts in each.
Observed behavior: The user requires a logical formula that checks for specific text (PASS) across adjacent or non-adjacent cells without altering the existing spreadsheet layout.

Before you start

Verify the exact cell references for your Reading and Writing attempt columns, as the best formula approach depends on whether these cells are adjacent to each other or separated by other confidential data columns.

Solution 1Recommended

Use IF, AND, and COUNTIF for Adjacent Attempt Cells

This method is recommended when the attempt cells for each subject are grouped together in continuous ranges.

By combining the IF, AND, and COUNTIF functions, you can easily check continuous ranges. COUNTIF counts how many times PASS appears. The AND function ensures both subjects meet the condition before the IF function returns the final PASS.

Select the target cell

Click on the cell where you want the final PASS/FAIL result to appear for the first student.

Input the nested formula

Enter the formula: =IF(AND(COUNTIF(B2:D2,"PASS")>=1,COUNTIF(E2:G2,"PASS")>=1),"PASS","FAIL"). Adjust B2:D2 and E2:G2 to match your actual subject ranges.

Apply to the remaining rows

Press Enter to calculate the result, then click and drag the fill handle at the bottom right of the cell downwards to apply the formula to the rest of the column.

Use IF, AND, and COUNTIF for Adjacent Attempt Cells

Formula Breakdown: COUNTIF(range,"PASS")>=1 ensures there is at least one passing attempt. The AND function requires this to be true for both the first and second range.

Solution 2

Use IF, AND, and OR for Non-Adjacent Attempt Cells

Use this approach if your spreadsheet layout cannot be changed and the attempt cells are scattered across different columns.

Seamless Formula Compatibility

Calculate Complex Grades Easily in WPS Spreadsheet

WPS Office fully supports advanced nested formulas like IF, AND, OR, and COUNTIF. You can manage complex conditional logic, grade calculations, and large datasets effortlessly using WPS Spreadsheet.

1. Open your workbook: Launch WPS Spreadsheet and open your student grading file.
2. Enter the formula: Select the target cell and type your nested IF and COUNTIF formula.
3. Drag to fill: Press Enter and use the fill handle to copy the logic down your entire list.

100% compatible with Microsoft Excel formulas, functions, and file formats.Intuitive formula builder with syntax highlighting to prevent errors.Lightweight, fast to load, and completely free to use for daily calculations.

microsoft office alternative - wps office

Frequently Asked Questions

How do I make the formula case-insensitive for the word PASS?

The COUNTIF function and standard equality checks (="PASS") in Excel are already case-insensitive. Typing "Pass", "pass", or "PASS" in the attempt cells will all be counted correctly by the formula.

Can I return different text instead of PASS or FAIL?

Yes. Simply change the "PASS" and "FAIL" arguments at the very end of the IF function to your desired text. Make sure to keep your new text enclosed in quotation marks, such as "Certified" and "Needs Retake".

What if I need to check three subjects instead of two?

You can add another COUNTIF or OR condition inside the AND function. For example: AND(COUNTIF(B2:D2,"PASS")>=1, COUNTIF(E2:G2,"PASS")>=1, COUNTIF(H2:J2,"PASS")>=1). All three conditions must be true for the final output to be PASS.

Excel & Data Problems