Formula Errors

How to Count Positive Values in a TAKE and FILTER Formula in Excel

Elise Williams Oct 9, 2026 869 views

Question details

The user wants to count cells containing values greater than zero within the first five populated columns of a nested TAKE and FILTER dynamic array formula.

How to Count Positive Values in a TAKE and FILTER Formula in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Extracting specific columns of data from a range and counting only the positive numbers within that dynamically extracted data array.
Observed behavior: Attempting to use the standard COUNTIF function on nested dynamic arrays (like TAKE and FILTER) often results in a #SPILL! or #VALUE! error because COUNTIF expects a physical range rather than an in-memory array.

Before you start

Ensure you are using a recent version of Excel (such as Microsoft 365 or Excel 2021) that supports Dynamic Array functions, as LET, TOROW, TAKE, and FILTER are not available in older versions.

Solution 1Recommended

Use LET with TOROW, TAKE, FILTER, and COUNTA

Combine these dynamic array functions to filter out non-positive numbers and count the remaining valid cells without triggering a spill error.

Using the LET function allows you to define intermediate calculation steps, making complex nested formulas easier to read and process. By defining the array first, you can safely apply conditions without relying on COUNTIF, which struggles with in-memory dynamic arrays.

Define the array with LET

Select the cell where you want the result. Start your formula with =LET(f, TAKE(TOROW($H2:R2, 1), , 5), ...) to extract the first five populated columns from your target range, ignoring blank cells.

Filter the positive values

Use the FILTER function on the defined variable 'f' to keep only values strictly greater than zero by adding FILTER(f, f>0) into the formula sequence.

Count the resulting items

Wrap the filtered array in the COUNTA function to count the number of cells that meet the criteria. The final formula structure is: =LET(f,TAKE(TOROW($H2:R2,1),,5),COUNTA(FILTER(f,f>0)))

Apply the formula

Press Enter to apply the formula. Excel will accurately return the count of positive numbers from the extracted columns without throwing an error.

Use LET with TOROW, TAKE, FILTER, and COUNTA

Formula Breakdown: TOROW collapses the range and removes blanks, TAKE extracts the first 5 elements, FILTER removes values less than or equal to 0, and COUNTA calculates the final tally.

Powerful Spreadsheet Software

Manage Complex Array Formulas Easily with WPS Spreadsheet

WPS Office Spreadsheet provides excellent support for advanced data analysis and complex dynamic formulas. You can seamlessly use array functions to calculate, filter, and extract data, ensuring your spreadsheets remain accurate and error-free.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your Excel workbook (.xlsx) containing the data range you need to calculate.
2. Enter the nested formula: Select the target cell and input the formula: =LET(f,TAKE(TOROW($H2:R2,1),,5),COUNTA(FILTER(f,f>0)))
3. Calculate the result: Press Enter. WPS Spreadsheet will process the dynamic array and accurately count the positive values within your defined range.

Full compatibility with Microsoft Excel formulas and .xlsx files.Built-in support for advanced functions to handle complex data extraction easily.Lightweight application that runs smoothly even with large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does COUNTIF return a spill error with dynamic arrays?

The COUNTIF function often struggles when its range argument is a dynamic array generated in memory by functions like FILTER or TAKE, rather than a physical range on the sheet. This causes it to return a #SPILL! or #VALUE! error. Using COUNTA combined with FILTER bypasses this limitation entirely.

What does the TOROW function do in this formula?

The TOROW function transforms an array or a specific range into a single row. By setting its second argument to 1 (e.g., TOROW(range, 1)), it automatically ignores blank cells, ensuring that only populated columns are passed onward to the TAKE function.

Can I modify the formula to count values greater than a different number?

Yes, you can easily modify the mathematical condition inside the FILTER function. For example, to count values greater than 10, you would simply change the parameter FILTER(f, f>0) to FILTER(f, f>10) within the LET formula.

Excel & Data Problems