Function Problems

Why COLUMNS() Returns 1 in Excel SCAN Formulas and How to Fix It

Maira Mehtab Oct 13, 2026 869 views

Question details

The user wants to understand why COLUMNS(b) evaluates to 1 within a SCAN LAMBDA function and needs a solution to properly reset the accumulator at the start of each row in a 2D range.

Why COLUMNS() Returns 1 in Excel SCAN Formulas

Product: Excel
Device & OS: not provided
Scenario: Writing a dynamic array formula using SCAN and LAMBDA to accumulate values across a 2D range, requiring a condition to detect the start of a new row.
Observed behavior: Because the SCAN function iterates value by value, the parameter passed to LAMBDA is a single cell value. As a result, COLUMNS(b) counts the columns in that single value and always returns 1, causing row-reset conditions to fail.

Before you start

Ensure you have a fundamental understanding of dynamic array functions, particularly LAMBDA and SCAN, as they process array data value by value rather than evaluating the entire range at once.

Solution 1Recommended

Use MIN(COLUMN(input)) within a LET Function

Identify the absolute starting column of your range and reset the SCAN accumulator whenever the current column matches this first column.

Instead of attempting to count the columns of the iterative variable, you can use the LET function to define your input range and find its absolute starting column. Then, you can compare the current cell's column number against this starting column to trigger the reset.

Define your variables with LET

Start your formula by declaring the input range. For example: =LET(input, $A$105:$D$106

Determine the first column

Add a variable to find the minimum column index of your input range: firstColumn, MIN(COLUMN(input))

Apply the SCAN and LAMBDA functions

Use SCAN to iterate through the input. Within LAMBDA, use an IF statement to check if COLUMN(b) equals firstColumn: SCAN(0, input, LAMBDA(accum, b, IF(COLUMN(b)=firstColumn, b, accum+b)))

Complete the formula

Close the parentheses to finish the LET function: =LET(input, $A$105:$D$106, firstColumn, MIN(COLUMN(input)), SCAN(0, input, LAMBDA(accum, b, IF(COLUMN(b)=firstColumn, b, accum+b))))

Use MIN(COLUMN(input)) within a LET Function

Understanding COLUMN vs COLUMNS: COLUMN(b) retrieves the absolute worksheet column index (e.g., Column A is 1), while COLUMNS(b) counts how many columns are in the argument 'b'. Since 'b' is a single cell value inside SCAN, COLUMNS(b) is always 1.

Solution 2

Use the MOD Function for Fixed Ranges

Calculate the relative column position using MOD to determine when the accumulator should reset.

Master Advanced Array Formulas with WPS Spreadsheet

WPS Office provides full support for modern dynamic array functions like LET, LAMBDA, and SCAN. You can build complex data processing workflows and execute advanced accumulator logics seamlessly.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the file where you need to perform data accumulation.
2. Select the target cell: Click on the cell where you want your dynamic array results to spill over.
3. Enter the SCAN and LAMBDA formula: Type your =LET(...) and SCAN(...) formula exactly as you would natively. WPS Spreadsheet fully interprets dynamic arrays.
4. Press Enter to calculate: Hit Enter. The results will automatically calculate and spill across the required rows and columns based on your accumulator logic.

Fully compatible with Microsoft Excel formulas and functions.Supports modern dynamic arrays including LAMBDA, SCAN, and LET.Lightweight, fast, and completely free to use.Familiar interface makes migrating your complex spreadsheets effortless.

microsoft office alternative - wps office

Frequently Asked Questions

Why does COLUMNS(b) return 1 instead of the total columns in my range?

In a SCAN LAMBDA formula, the function iterates through the given range value by value. The parameter 'b' represents just one single cell value during each iteration. Because a single cell is exactly one column wide, COLUMNS(b) always evaluates to 1. To get the range's total columns, reference the full range directly outside the LAMBDA context.

How does the SCAN function process 2D arrays?

The SCAN function processes a 2D array row by row. It moves from left to right across the first row, and once it reaches the end, it drops down to the beginning of the next row. This is why accurately detecting the first column is crucial for resetting row-based accumulations.

Can I use the LET function to improve formula performance?

Yes. Using the LET function allows you to declare variables such as your input range or starting column index. This reduces formula redundancy, makes it easier to read, and improves performance because intermediate results are only calculated once.

Excel & Data Problems