Numbers BYCOL: Column Totals, Means and LAMBDA Examples

• Introduced: The original introduction release and date are not established by the checked references. LAMBDA’s separate release history does not establish this helper’s first release.
• Availability: Currently documented in Apple’s Functions Help 15.4 documentation edition; no final supported-version endpoint is established.
• Scope: Shared iWork reference. Function-specific historical Mac, iPad, iPhone and iCloud endpoints are unverified.
• Checked: October 14, 2026, against the official BYCOL reference. Examples were independently calculated; no native Numbers test was performed.

BYCOL(array, LAMBDA(parameter, calculation))

Both the array and the LAMBDA expression are required. The parameter identifies the column supplied to the subformula; the calculation is required and belongs last. Apple’s BYCOL reference illustrates column maxima and a column-level SUMPRODUCT calculation.

In LAMBDA(readings,SUM(readings)), readings refers to the current column. It does not name a separate worksheet cell or the entire matrix. There is no documented optional argument for ascending order, precision or a different direction.

Create DailyCounters, with eight columns A:H and thirteen rows, including one header row. Enter Day in A1 and Counter A, Counter B and Counter C in B1:D1. Enter the following numeric counts in B2:D5; the day labels in A are not part of the calculation range.

Put indices 1,2,3 in B7:D7. In B8 enter:

=INDEX(BYCOL($B$2:$D$5,LAMBDA(readings,SUM(readings))),1,B7)

Fill B8 across through D8. The anchored input does not move; B7 becomes C7 and D7 to select the second and third results. Expect 36,20,24.

The INDEX entry supports values from a returned array. For these column results, use result row one and the requested column index. Do not use worksheet column numbers two, three and four as though they were positions inside the three-result array.

In B9 enter =INDEX(BYCOL($B$2:$D$5,LAMBDA(readings,AVERAGE(readings))),1,B7) and fill across to D9. In B10 enter =INDEX(BYCOL($B$2:$D$5,LAMBDA(readings,MAX(readings)-MIN(readings))),1,B7) and fill across to D10.

In B11 enter =SUM(B2:B5) and fill across to D11. In B12 enter =B8-B11 and fill across to D12.

Counter A has the largest total, while A and B share the same four-day range. Total, mean and range are different summaries even when they use the same column.

Put =SUM(B8:D8) in G2 and =SUM(B2:D5) in G3; both give 80. In E2 enter =SUM(B2:D2) and fill through E5 for daily combined totals 16,22,17,25. Their sum also gives eighty.

In G4 enter =SUM(B9:D9) for 20, the sum of the three column means. In G5 enter =AVERAGE(B2:D5) for approximately 6.6666666667, the mean of the twelve individual counter readings. In G6 enter =AVERAGE(B9:D9) for the same approximately 6.6666666667. In G7 enter =AVERAGE(E2:E5) for 20, the mean of the four combined daily totals.

These values answer different questions. Each day has three numeric counter observations, and each counter has four numeric days. Those equal group sizes explain why the average of the column means agrees with the all-cell mean here. The sum of column means instead agrees with the mean daily combined total.

The equal-size identity is not a general rule for groups containing different numbers of usable observations. Do not average subgroup means blindly after changing completeness or input types. The example has no missing observations, so it does not establish a mixed-data rule for BYCOL.

Copy DailyCounters to DoubledFirstCounter and change B2:B5 to 16,20,12,24, leaving C:D intact. B8:D8 and B11:D11 become 72,20,24. The means in row 9 become 18,5,6, and the ranges in row 10 become 12,6,4. Row 12 remains zero.

The daily combined totals E2:E5 become 24,32,23,37. G2 and G3 become 116; G4 and G7 become 29; G5 and G6 become approximately 9.6666666667. Only the first counter’s values were doubled, so this is not a uniform rescaling of every reading.

This control also checks that the subformula receives one column at a time. Accidentally replacing readings with the full matrix reference would repeat a whole-table result instead of giving the distinct column totals.

Create VendorColumns, with six columns A:F and six rows, using no header row for this grid. Enter 3,7,1 in A1:C1, 9,6,2 in A2:C2 and 8,5,4 in A3:C3. Put 1,2,3 in A5:C5.

In A6 enter =INDEX(BYCOL($A$1:$C$3,LAMBDA(readings,MAX(readings))),1,A5) and fill across to C6. Expect 9,7,4, matching the current official example. The separate column sums are 20,18,7; you can check them with =SUM(A1:A3) in E1, =SUM(B1:B3) in E2 and =SUM(C1:C3) in E3.

Apple documents the spilled-range operator # for referencing an array produced by a formula. This guide uses scalar INDEX extraction instead, so each result cell has an explicit formula and destination. That is not a native spill-layout test or a claim that spilling is unavailable.

The LAMBDA reference says definitions are local to their cell and LAMBDA functions cannot be nested. Each formula here contains one LAMBDA, supplied to the outer row or column function. Copying the formula copies that local definition; it does not create a workbook-wide named function.

The fixtures use numbers with compatible units. The short helper entry does not establish every mixed-type array, empty range, error cell, multi-result calculation or extreme numerical limit. Exact malformed-parameter errors and cross-client workbook behavior remain unverified. Keep the inputs and output cells separate.

English examples use decimal points and commas. Apple’s functions overview specifies semicolon argument separators where the decimal separator is a comma. For another table, select the intended cells while editing so Numbers inserts the qualified reference, such as TableName::B2.

BYROW applies the calculation to rows instead. LAMBDA defines the subformula and INDEX selects a result. SUM, AVERAGE, MAX and MIN provide the arithmetic used here.