Function Problems

How to Use Excel TOCOL and FILTER with Multiple OR Conditions

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs an Excel formula using TOCOL and FILTER to extract a list of dates within a four-week period that correspond to a status of either 'O' (Off) or 'BH' (Bank Holiday).

Product: Excel
Device & OS: not provided
Scenario: Filtering a horizontal range of dates to extract specific columns where the date falls within a specific range AND the corresponding status matches one of multiple specific text values.
Observed behavior: The user needs to correctly format the inclusion logic within the FILTER function to evaluate multiple OR conditions alongside multiple AND conditions before converting the result into a single column using TOCOL.

Before you start

Ensure you are using a version of Excel or WPS Office that supports Dynamic Array functions like TOCOL and FILTER, and verify that your date and status ranges match perfectly in size.

Solution 1Recommended

Combine TOCOL and FILTER Using Array Boolean Logic

Use the addition operator (+) to create an OR condition for the 'O' and 'BH' text values, and the multiplication operator (*) for the AND conditions controlling the date range.

In Excel array formulas, you cannot use the standard OR() or AND() functions within the FILTER function's include argument. Instead, you must use mathematical operators. Multiplication (*) acts as AND, requiring both conditions to be true. Addition (+) acts as OR, requiring only one of the conditions to be true.

Define the target date range

Identify the row containing your dates (e.g., J2:MH2). This will serve as the first argument in your FILTER function.

Set up the AND conditions (Date Range)

Create the logical test for your date range using multiplication. For example, to check if dates are greater than or equal to a start date in F1 and within 4 weeks: `(J2:MH2>=F1)*(J2:MH2<=F1+28)`.

Set up the OR conditions (Status Values)

Create the logical test for the statuses using addition. For a status row in J3:MH3 looking for 'O' or 'BH', use: `((J3:MH3="O")+(J3:MH3="BH"))`.

Combine into the final formula

Nest the FILTER function inside TOCOL, multiplying the date logic by the status logic: `=TOCOL(FILTER(J2:MH2, (J2:MH2>=F1)*(J2:MH2<=F1+28)*((J3:MH3="O")+(J3:MH3="BH")), ""))`. Press Enter to spill the results.

Correct Bracket Placement: Pay close attention to the parentheses. The entire OR statement `((J3:MH3="O")+(J3:MH3="BH"))` must be enclosed in its own set of brackets before being multiplied by the AND conditions.

Advanced Formula Support in WPS Office

Efficiently Filter and Extract Data with WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic array functions like FILTER and TOCOL, allowing you to seamlessly process complex criteria and multiple conditions just like in Excel.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
2. Select the destination cell: Click on the cell where you want your single-column list of dates to begin.
3. Enter the formula: Type your combined dynamic formula (e.g., `=TOCOL(FILTER(...))`) and press Enter.
4. Review the dynamic array: WPS Spreadsheet will automatically process the formula and spill the filtered dates into the column.

Fully compatible with Microsoft Excel array formulas and dynamic functions.Easily filter dates and extract specific values based on complex AND/OR conditions.Lightweight, fast, and features a familiar tabbed interface for high productivity.Free to use with comprehensive spreadsheet analysis tools.

microsoft office alternative - wps office

Frequently Asked Questions

How do I add a third status condition to my FILTER formula?

You can add additional conditions to your OR logic by adding another plus sign (+) and condition within the same parentheses group. For example, to also include 'AL' (Annual Leave), use: `((J3:MH3="O")+(J3:MH3="BH")+(J3:MH3="AL"))`.

Why am I getting a #CALC! error with my FILTER function?

The #CALC! error usually occurs if the FILTER function evaluates to an empty array (meaning no dates matched your conditions). To handle this gracefully, use the third argument of the FILTER function to output a custom string, like this: `=FILTER(range, conditions, "No dates found")`.

Can I use TOCOL to ignore blank cells and errors?

Yes, TOCOL includes a built-in argument for this. Provide '1' as the second argument to ignore blanks (`=TOCOL(array, 1)`), '2' to ignore errors, or '3' to ignore both blanks and errors.

Does WPS Spreadsheet support the TOCOL and FILTER functions?

Yes, the latest versions of WPS Spreadsheet fully support dynamic array functions, including TOCOL, FILTER, and TOROW, ensuring full compatibility with advanced Excel formulas.

Excel & Data Problems