Function Problems

How to Use Spill Formulas and Arrays in COUNTIFS Criteria Range

Maira Mehtab Sep 24, 2026 872 views

Question details

The user is attempting to use an in-memory array (generated by a spilled TEXT formula) as the criteria range for a COUNTIFS function, but it returns an error because COUNTIFS expects a standard physical worksheet range.

Product: Spreadsheet
Device & OS: not provided
Scenario: Counting occurrences of specific weekday names that are dynamically extracted from a date column using the TEXT function.
Observed behavior: The COUNTIFS formula fails because it is unable to process an array in memory (such as the output of the TEXT function) as its criteria range, which strictly requires actual cell references.

Before you start

Verify that your target column contains valid dates and ensure your spreadsheet software supports array calculations before applying the alternative formulas below.

Solution 1Recommended

Use the SUM Function with Array Multiplication

This is the most robust and highly recommended way to count items based on array manipulations without relying on COUNTIFS.

Since COUNTIFS strictly requires a physical worksheet range (e.g., C2:C32) and cannot accept an array in memory, you can evaluate the condition as a boolean array wrapped in a SUM function.

When you multiply the TRUE/FALSE boolean array by 1, it automatically converts the results into 1s and 0s, which SUM can then easily total up.

Write the logical array condition

Enter the condition `TEXT($C$2:$C$32,"ddd")=$A2` to check which dates match your target weekday in cell A2.

Convert booleans to numerical values

Wrap the condition in parentheses and multiply by 1: `1*(TEXT($C$2:$C$32,"ddd")=$A2)`.

Sum the array results

Wrap the entire expression in the SUM function: `=SUM(1*(TEXT($C$2:$C$32,"ddd")=$A2))` and press Enter to get the final count.

Legacy Excel Compatibility: If you are using an older version of Excel that does not support dynamic arrays natively, you can use SUMPRODUCT instead of SUM: `=SUMPRODUCT(1*(TEXT($C$2:$C$32,"ddd")=$A2))`.

Solution 2

Use the FILTER and ROWS Functions

Best for modern spreadsheet environments supporting dynamic arrays, filtering the dataset first and then counting the returned rows.

Simplify Complex Formulas with WPS Office

Process Advanced Arrays Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic arrays, SUMPRODUCT alternatives, and complex criteria matching. You can easily manage dates, in-memory arrays, and large datasets without compatibility issues.

1. Download WPS Office: Install WPS Office and open the WPS Spreadsheet application.
2. Open Your Dataset: Open your existing .xlsx or .csv file containing the date logs.
3. Apply Array Formula: Enter the alternative array formulas like `=SUM(1*(...))` exactly as you would in standard spreadsheet applications.
4. Drag to Fill: Drag the fill handle to apply the array calculation down your data summary table instantly.

Fully supports dynamic arrays and spill formulas natively.100% compatible with Microsoft Excel formulas like FILTER, SUMPRODUCT, and COUNTIFS.Lightweight architecture runs smoothly even with massive data calculations.Built-in formula error-checking and intelligent tooltips.

microsoft office alternative - wps office

Frequently Asked Questions

Why does COUNTIFS return a #VALUE! error when using an array?

The COUNTIFS function is strictly programmed to accept physical ranges of cells (like A1:A10) for its criteria_range arguments. When you pass an array function (like TEXT) or a spilled array into it, it cannot process the in-memory array and automatically throws an error.

Can I use SUMPRODUCT instead of SUM for counting logic arrays?

Yes. Using `=SUMPRODUCT(1*(TEXT($C$2:$C$32,"ddd")=$A2))` will achieve the exact same result. SUMPRODUCT is especially useful in older versions of spreadsheets that do not natively support dynamic array formulas.

Does WPS Spreadsheet support dynamic array formulas?

Yes, modern versions of WPS Spreadsheet fully support dynamic array formulas and functions natively, including spill functions like FILTER, SORT, and UNIQUE.

How do I extract just the weekday from a date for counting?

Use the TEXT function combined with the format code "ddd" (for a short day name, e.g., Mon) or "dddd" (for the full day name, e.g., Monday). The syntax is `=TEXT(A2,"ddd")`.

Excel & Data Problems