Formula Errors

How to Fix Excel COUNTIF and FILTER Spilled Array #VALUE! Error

Muhammad Talha Sep 28, 2026 869 views

Question details

The user is attempting to filter a list using a combination of COUNTIF and FILTER functions on spilled arrays, but it results in a #VALUE! error despite helper formulas working individually.

How to Fix Excel COUNTIF and FILTER Spilled Array #VALUE! Error

Product: Excel
Device & OS: not provided
Scenario: Comparing two arrays to return unique values from a source list that are not present in an exclusion list using dynamic array functions.
Observed behavior: The combined formula evaluates to a #VALUE! error because COUNTIF struggles to process spilled dynamic arrays as criteria parameters in certain contexts.

Before you start

Ensure that your spreadsheet application is updated to a version that fully supports dynamic array functions (such as Office 365, Excel 2021, or WPS Office) and verify that your source ranges do not contain mismatched dimensions.

Solution 1Recommended

Replace COUNTIF with UNIQUE, FILTER, ISERROR, and XMATCH

Avoid the #VALUE! error by completely removing COUNTIF and using an ISERROR and XMATCH combination to accurately filter out specific array items.

When dealing with dynamic arrays, the COUNTIF function can occasionally fail to evaluate a spilled array as its criteria argument, which triggers a #VALUE! error. A much more reliable method to cross-check two arrays is using the XMATCH function.

Identify your data ranges

Determine your source data range (for example, G4:G14) and the exclusion data range you want to filter out (for example, I4:I6).

Set up the XMATCH function

Click an empty cell and type XMATCH(G4:G14, I4:I6). This function will look up the source items within the exclusion array.

Wrap with ISERROR

Enclose the XMATCH formula inside an ISERROR function like this: ISERROR(XMATCH(G4:G14, I4:I6)). This returns TRUE for items that are not found in the exclusion list.

Combine with FILTER and UNIQUE

Wrap the entire logic inside FILTER and UNIQUE. Your final formula should be: =UNIQUE(FILTER(G4:G14, ISERROR(XMATCH(G4:G14, I4:I6)))). Press Enter to generate the spilled array.

Replace COUNTIF with UNIQUE, FILTER, ISERROR, and XMATCH

Tip for Troubleshooting: When asking for formula help on forums, always provide sample data in a format that can be copied and pasted directly into a spreadsheet, rather than providing screenshots.

Solution 2

Use LET and ISNA for Complex Step-by-Step Array Filtering

Utilize the LET function to assign names to calculation results, making complex multi-condition filtering much easier to read and preventing calculation errors.

Smart Spreadsheet Tool

Easily Manage Dynamic Arrays with WPS Spreadsheet

WPS Spreadsheet fully supports dynamic arrays, modern formulas like FILTER and UNIQUE, and complex data filtering tasks. You can seamlessly resolve #VALUE! errors and handle spilled arrays in a highly compatible environment.

1. Open WPS Spreadsheet: Launch WPS Office and open your .xlsx file that contains the problematic arrays.
2. Select the Target Cell: Click on the cell where you want the new filtered dynamic array to begin spilling.
3. Input the Dynamic Formula: Enter the corrected formula: =UNIQUE(FILTER(G4:G14,ISERROR(XMATCH(G4:G14,I4:I6)))) into the formula bar.
4. Execute the Formula: Press Enter. WPS Spreadsheet will calculate the logic and perfectly spill the dynamic array without generating a #VALUE! error.

Fully compatible with Microsoft Excel formats (.xlsx) and dynamic array formulas.Natively supports advanced functions like FILTER, UNIQUE, and XMATCH.Free, lightweight, and features an intuitive interface for advanced data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why does combining COUNTIF and FILTER cause a #VALUE! error?

COUNTIF evaluates arrays differently and can fail to properly process a dynamically spilled array as its criteria argument. This limitation in the calculation engine causes the function to break and output a #VALUE! error. Using XMATCH or MATCH instead bypasses this limitation.

What is a spilled array in spreadsheet formulas?

A spilled array is the result of a dynamic array formula (like FILTER or UNIQUE) that returns multiple values. Instead of sitting inside a single cell, the results automatically 'spill' downwards or rightwards into adjacent empty cells.

Can I use the LET function in WPS Spreadsheet?

Yes, WPS Spreadsheet supports advanced dynamic array functions, including the LET function. This allows you to define variables within your formula to improve both performance and readability.

How do I fix the #VALUE! error if my ranges are of different sizes?

Ensure that the arrays being compared or evaluated within your FILTER or XMATCH functions cover the exact same number of rows or columns. Mismatched array dimensions are a common cause of #VALUE! and #N/A calculation errors.

Excel & Data Problems