BYCOL in Excel: Monthly Totals and Column-by-Column Checks

• First introduced: Preview announced July 26, 2021, for Windows Beta 2108 (build 14312.20008) and Mac 16.52 (build 21072100). These are announced preview minima. Official introduction.
• Current documented status: the dedicated help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac. Availability depends on the installed build.
• Evidence checked: October 14, 2026. Official BYCOL help. Examples below were independently calculated, not executed in Excel.

=BYCOL(array,LAMBDA(column,calculation))

The LAMBDA has one named input: the whole current column. Its calculation must produce one value for that column. Microsoft documents an incorrect-parameter #VALUE! error for an invalid LAMBDA signature and a #CALC! error when a single result is not returned. BYCOL reference

Use comma argument separators in an English-language locale. Do not type optional-argument notation or add extra LAMBDA parameters to represent worksheet columns.

Enter this table in A1:D4. A1 contains Team; B1:D1 contain the three month labels. All quantities in B2:D4 are numbers.

Use F2 onward for results. The calculations deliberately exclude the headings and team names.

=BYCOL(B2:D4,LAMBDA(month,SUM(month)))

Expected output is one row of three numbers: 17, 39, 63. January totals 10+5+2; February totals 20+15+4; March totals 30+25+8. Put the month labels above the output yourself if needed: BYCOL returns the calculation results, not copied source headings.

Compare the first result with =SUM(B2:B4). Both should be 17. That small independent comparison is useful before extending the formula to twelve months.

=BYCOL(B2:D4,LAMBDA(month,MAX(month)))

Expected output: 10, 20, 30 across three columns. These are values, not the names of winning teams. A tie does not create a second result because the LAMBDA still returns one scalar per column.

If the report needs the team name, define a tie-handling rule and perform a separate lookup against the corresponding column. Do not label the maximum itself as a record identifier.

Put 15 in F1, then use:

=BYCOL(B2:D4,LAMBDA(month,SUM(--(month>=$F$1))))

Expected output: 0, 2, 2. No January quantity reaches 15; East and West qualify in February and March. The double unary converts the Boolean comparison results into ones and zeros before summing.

Changing F1 to 25 produces 0, 0, 2. Keep F1 outside the returned array and the source range. This formula tests the supplied numeric data; a mixed-type import needs a separate data-type check.

=BYCOL(B2:D4,LAMBDA(month,SUM(month)/SUM($B$2:$D$4)))

Expected fractions are 17/119, 39/119, 63/119, approximately 14.2857%, 32.7731%, 52.9412% after percentage formatting. They sum to 100% before display rounding. The fixed denominator is the complete numeric table, while the numerator changes for each column.

This example assumes a nonzero grand total. Decide explicitly what a zero denominator should mean rather than masking it automatically with zero percent.

A vertical result usually indicates that the formula is using BYROW instead. A two-dimensional result from the LAMBDA violates BYCOL’s single-result requirement; return SUM, MAX or another scalar calculation. An occupied destination can obstruct the spill. Put the formula outside an Excel table when a spilling output is required.

Do not pass LAMBDA(a,b,...) to BYCOL merely because your source contains two columns: BYCOL supplies one entire column at a time. Exclude a totals row from the input if that row already aggregates the monthly data, or the example will double-count it.

For a small fixed report, separate SUM formulas may be easier to maintain. BYCOL is valuable when one reusable rule should be applied consistently across many columns.

• LAMBDA in Excel
• SUM in Excel
• MAX in Excel

Verification: Inputs are fictional. Expected values and array shapes were independently calculated from these inputs; no native Excel execution or screenshot is claimed. See the editorial testing standards.