BYCOL function
Quick answer
BYCOL runs a LAMBDA on each column of a range and returns one result per column, in a single formula that spills across.
- Syntax
=BYCOL(array, lambda)- Most common use
=BYCOL(B2:D4,LAMBDA(c,SUM(c)))returns 36 (Each region's total)
Use it for column totals, averages or spreads under a table, or any per-column calculation you would otherwise copy across.
- Available in
- Excel 2024 and Microsoft 365
- Category
- Dynamic array functions
- Returns
- A one-row array with one result per column.
- Google Sheets
- Available in Google Sheets.
Syntax
=BYCOL(array, lambda)
| Argument | Required? | What it is |
|---|---|---|
array | Required | The range or array to process column by column. |
lambda | Required | A LAMBDA with one input, the column, that returns a single value. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| B5 | =BYCOL(B2:D4,LAMBDA(c,SUM(c))) | 36spills 1 × 3 | Each region's total |
| B6 | =BYCOL(B2:D4,LAMBDA(c,AVERAGE(c))) | 12.00spills 1 × 3 | Each region's average |
| B7 | =BYCOL(B2:D4,LAMBDA(c,MAX(c)-MIN(c))) | 6spills 1 × 3 | Each region's range, best minus worst |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Month | North | South | West |
| Jan | 12 | 8 | 20 |
| Feb | 15 | 11 | 7 |
| Mar | 9 | 14 | 13 |
| 36 | 33 | 40 | |
| 12.00 | 11.00 | 13.33 | |
| 6 | 6 | 13 |
Practice BYCOL
Graded instantly, then re-checked on changed data.
Column totals
B8, use BYCOL to total each region's column in B2:E7, one result per column.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #CALC! | The LAMBDA returns more than one value per column. |
| #SPILL! | The cells to the right aren't empty. |
Tips
- =BYCOL(B2:M50,LAMBDA(c,COUNTBLANK(c))) shows how many gaps each column has.
Questions
How do I total every column with one formula?
=BYCOL(B2:F20,LAMBDA(c,SUM(c))) spills a total under each column.
What is the difference between BYROW and BYCOL?
BYROW gives one result per row, down a column. BYCOL gives one per column, across a row.
Updated Checked by running: every formula on this page was run by our formula engine on .