Calculation Issues

How to Make Excel Formula Stop Counting When Another Cell Says Yes

WPS Content Manager Oct 9, 2026 869 views

Question details

The user needs an Excel formula that calculates overdue days to stop counting and return a blank or specific value when a designated status cell indicates the issue is resolved (e.g., says 'Yes').

How to Make an Excel Formula Stop Counting When Another Cell Says Yes

Product: Excel
Device & OS: not provided
Scenario: Tracking project deadlines or overdue days where the calculation needs to halt automatically once a corresponding status column is marked as 'Yes'.
Observed behavior: The current formula continues calculating the overdue date difference dynamically even after the resolution indicator changes to 'Yes'.

Before you start

Ensure you know the exact cell reference containing your 'Yes' status indicator (e.g., column L) before editing your formula.

Solution 1Recommended

Add a Status Check to the Existing IF and OR Formula

Expand your existing OR function to check if the status cell equals 'Yes', forcing the formula to return a blank instead of calculating days.

By nesting an additional condition inside your existing OR function using the LOWER function, you can instruct Excel to check the resolution status regardless of text capitalization. If the status says 'Yes', the formula bypasses the date calculation and outputs an empty string.

Select the formula cell

Click on the cell containing your current overdue days calculation (for example, cell P2).

Edit the formula in the Formula Bar

Click into the Formula Bar at the top of the screen and add LOWER(L2)="yes" inside the OR statement. Your updated formula should look like this: =IF(OR(M2="",J2="",LOWER(L2)="yes"),"",U$1-J2).

Apply and test the formula

Press Enter to save the changes. Type 'Yes' or 'yes' into cell L2 to verify that the calculation cell now becomes blank.

Copy the formula down the column

Click and drag the fill handle at the bottom-right corner of the selected cell to copy the newly updated formula to the remaining rows in your spreadsheet.

Add a Status Check to the Existing IF and OR Formula

Return Zero Instead of a Blank Cell: If you prefer the cell to display a '0' instead of remaining blank when the issue is resolved, simply replace the middle quotation marks in the formula with a zero: =IF(OR(M2="",J2="",LOWER(L2)="yes"),0,U$1-J2).

Effortlessly Manage Complex Logical Formulas in WPS Spreadsheet

WPS Spreadsheet provides a seamless, fast, and completely compatible environment for writing, editing, and managing complex IF/OR formulas for tracking project deadlines and statuses.

1. Open your tracking sheet: Launch WPS Spreadsheet and open the workbook containing your overdue days tracker.
2. Enter the logical formula: Select the target cell and type =IF(OR(M2="",J2="",LOWER(L2)="yes"),"",U$1-J2), then press Enter.
3. Apply across rows: Drag the fill handle downward to instantly apply the condition to all your tracking entries.

100% compatible with Microsoft Excel formulas, functions, and formattingSmart auto-completion for complex logical formulas like IF, OR, and LOWERFree, lightweight, and fast-loading spreadsheet solutionBuilt-in error checking to help troubleshoot complex formula structures quickly

microsoft office alternative - wps office

Frequently Asked Questions

How do I freeze the overdue days count when the status changes to yes instead of turning blank?

A standard IF formula updates dynamically, meaning changing the status to 'Yes' will replace your calculated days with a blank or zero. To freeze the exact number of days passed at the time of resolution, you will need to either manually copy and paste the cell as a 'Value', or use a VBA macro to lock the calculation when 'Yes' is triggered.

Why does the formula use the LOWER function?

Using LOWER(L2) converts the text inside cell L2 to lowercase before checking it against your criteria. This makes the formula case-insensitive, meaning it will successfully recognize 'Yes', 'yes', or 'YES' without failing.

Can I make the formula stop counting based on multiple different status words?

Yes, you can add as many conditions as you need inside the OR function. For example, to stop counting if the status is either 'Yes' or 'Completed', you would write: OR(M2="",J2="",LOWER(L2)="yes",LOWER(L2)="completed").

Excel & Data Problems