Practice free

BYCOL function

BYCOL runs a calculation on each column of an array and returns the results side by side in one row, such as the maximum of every column. The calculation must return one value per column.

Example

Read it as a sentence: go through each column of D2:E7, find the largest value in it, and show the results, starting with 20 for the Qty column.

Practice this in the app

Syntax

BYCOL(array, lambda)

array (required)
The range or array to process column by column.
lambda (required)
A LAMBDA taking one column and returning a single value.

Returns: A single row with one result per column.

One BYCOL formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=BYCOL({1,2;3,4},LAMBDA(c,SUM(c))) 4 Sum each column
=BYCOL({1,2;3,4},SUM) 4 Eta-reduced function
=BYCOL(Items!D2:E7,LAMBDA(c,MAX(c))) 20 Max of columns
=BYCOL(Items!B1:E1,LAMBDA(c,LEN(c))) 4 Text
=BYCOL({1,2;3,4},LAMBDA(c,c*2)) #CALC! Lambda returns array
=BYCOL({1,2},LAMBDA(a,b,a)) #VALUE! Two-parameter lambda
=BYCOL({1,2},5) #VALUE! Not a lambda

The Items sheet these formulas read

Watch out for

  • The lambda must return one value, not an array.
  • Needs LAMBDA support, so it is absent from older versions.

Where it works

Excel
BYCOL arrived in Excel 2024. Older versions do not have it. Dynamic-array LAMBDA family; in 365 and Excel for the web, and Excel 2024. Check older perpetual versions.
Google Sheets
Same name, same behaviour for everyday use.

Part of Logical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .