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.
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.
| Formula | Excel shows | Case |
|---|---|---|
=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 .