Formula Errors

How to Return Blank for Missing Cells Using UNIQUE and FILTER in Excel

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs an Excel formula utilizing UNIQUE and FILTER with date criteria to return a blank result if any specific reference cells (F13, G13, or H13) are empty.

Product: Excel
Device & OS: not provided
Scenario: Filtering data using dynamic array formulas based on date conditions, but needing to prevent calculation errors or unwanted outputs when input cells are missing data.
Observed behavior: The goal is to output an empty string instead of an error or default value when dependent cells (F13, G13, H13) are left blank.

Before you start

Ensure your version of Excel or WPS Spreadsheet supports dynamic array formulas like UNIQUE and FILTER, as older versions (prior to Microsoft 365 or Excel 2021) may not recognize these functions.

Solution 1Recommended

Use COUNTA to Check for Missing Cells (Recommended)

Using COUNTA is highly reliable because it correctly handles cells containing formulas that return empty text, unlike the ISBLANK function.

By counting the number of non-empty cells in the target range, we can ensure the main UNIQUE and FILTER logic only runs when all required inputs are present. If the count is less than expected, the formula outputs a blank string.

Select the Target Cell

Click on the cell where you want the filtered data to begin spilling.

Set Up the COUNTA Condition

Begin your formula with an IF statement checking the count of non-empty cells in your reference range. For example: =IF(COUNTA(F13:H13)<3, "", ...)

Append the Main Formula Logic

Insert your UNIQUE and FILTER functions into the 'value_if_false' section of the IF statement. The complete formula will look like this: =IF(COUNTA(F13:H13)<3,"",UNIQUE(IF(E13="One Time",FILTER(F13,(F13>=DATE(YEAR(TODAY()),1,1))*(F13<=DATE(YEAR(TODAY()),12,31))),FILTER(H13,(H13>=DATE(YEAR(TODAY()),1,1))*(H13<=DATE(YEAR(TODAY()),12,31)))))))

Apply the Formula

Press Enter to execute the formula. If F13, G13, or H13 is blank, the cell will remain blank.

Better than ISBLANK: This method is superior when dealing with cells that might contain invisible strings generated by other formulas, which ISBLANK would incorrectly identify as non-blank.

Solution 2

Use ISBLANK with an OR Function

A straightforward approach using ISBLANK to explicitly check each cell individually, best used when you know the cells will be completely empty and won't contain invisible formula outputs.

Dynamic Arrays in WPS Spreadsheet

Easily Manage Complex Formulas with WPS Office

WPS Spreadsheet fully supports modern dynamic array formulas like UNIQUE and FILTER, allowing you to build complex data extraction tools with ease and full compatibility.

1. Open Your Data: Launch WPS Spreadsheet and open your workbook containing the dataset.
2. Select Output Location: Click on the specific cell where you want the dynamically filtered data to appear.
3. Input the Formula: Type or paste your combined IF, UNIQUE, and FILTER formula into the formula bar.
4. Calculate Results: Press Enter to execute the formula and let WPS Spreadsheet dynamically spill the results into adjacent empty cells.

Fully supports UNIQUE, FILTER, COUNTA, and other dynamic array functions.100% compatible with Microsoft Excel (.xlsx) file formats and formula syntax.Free, lightweight, and features an intuitive interface for managing large datasets.Advanced calculation engine handles dynamic spilling of results automatically.

microsoft office alternative - wps office

Frequently Asked Questions

Why does ISBLANK return FALSE when a cell looks empty?

ISBLANK evaluates to FALSE if the cell contains a formula that returns an empty string (""). In these cases, the cell is not truly blank. Using COUNTA or checking if the cell equals "" is a more reliable method.

What versions of Excel support UNIQUE and FILTER?

UNIQUE and FILTER are dynamic array functions available in Microsoft 365, Excel 2021, Excel for the Web, and modern versions of WPS Office. In older versions, these functions will return a #NAME? error.

How do I handle #CALC! errors in FILTER formulas?

The #CALC! error typically occurs when the FILTER function finds no matching records based on your criteria. You can handle this gracefully by using the optional [if_empty] argument built directly into the FILTER function, such as: FILTER(A1:A10, B1:B10="Criteria", "")

Excel & Data Problems