This function groups an array by columns by application of a LAMBDA function to each column.

Sample Usage

BYCOL(A1:C3,LAMBDA(column,MAX(column)))

BYCOL(A1:C3,LAMBDA(column,SUM(column)))

Syntax

BYCOL(array_or_range,LAMBDA)

  • array_or_range: An array or range to be grouped by columns.
  • LAMBDA: A LAMBDA that’s applied to each column in the given array or range to obtain its grouped value.
    • Syntax: LAMBDA(name,formula_expression)
    • Requirements:
      • The LAMBDA must have exactly 1 name argument along with a formula_expression which uses that name. The name resolves to the current column being grouped when the LAMBDA is applied.

Notes

  • The passed LAMBDA should accept exactly 1 name argument, otherwise an #N/A error is returned. This argument corresponds to a column in the input array.
  • Every column should be grouped to a single value. Array results for grouped values aren’t supported.
  • A named function can be passed for the LAMBDA parameter and behaves like a LAMBDA in this case. Learn more about named functions.
    • There should be exactly 1 argument placeholder defined for it.
    • Parenthesis shouldn’t follow the named function.

Examples

Returns a 1x3 row-array with max of each column

Example data:

ABC
1357
2435
3124

Example: =BYCOL(A1:C3,LAMBDA(column,MAX(column)))

Result:

457

Returns the name of the sales representative with average sales greater than or equal to 30

Example data:

1AllyBrianLily
220192010
320205015
420213030

Example: =FILTER(B1:D1,BYCOL(B2:D4,LAMBDA(col,AVERAGE(col)>=30)))

Result:

Ally

Returns a 1x3 array with the difference between max and min value of each column using a Named function as LAMBDA

Make a Copy

Example data:

ABC
1357
2435
3124

Example: =BYCOL(A1:C3,MAX_MIN_DIFF)

Named function: MAX_MIN_DIFF is a named function which outputs the difference between the maximum value and the minimum value.

Formula definition: =MAX(col)-MIN(col), where col is an argument placeholder defined for MAX_MIN_DIFF.

Result:

333

Common Errors

The passed LAMBDA doesn’t have exactly 1 name argument

If the passed LAMBDA doesn’t have exactly 1 name argument, and 1 formula expression as an argument, this error occurs:

“Wrong number of arguments to LAMBDA. Expected 1 argument, but got 2 arguments.”

Example:=BYCOL(C1:C4,LAMBDA(a,b,a+b))

In this example, LAMBDA was given 2 name arguments when it only needs 1.

The last parameter of BYCOL wasn’t a LAMBDA

If the last parameter of BYCOL wasn’t a LAMBDA, this error occurs:

“Argument must be a LAMBDA.”

Example: =BYCOL(C1:C4,4)

The LAMBDA passed to BYCOL was incorrect

If 1 or more name arguments weren’t valid, this error occurs:

“Argument 1 of function LAMBDA is not a valid name.”

Example: =BYCOL(C1:C4,LAMBDA(C1,C1+1))

In this example, C1 is an invalid name since it clashes with a range.

The application of LAMBDA on the input array doesn’t group each column to a single value

If the application of LAMBDA on the input array doesn’t group each column to a single value, this error occurs:

“Single value expected. Nested array results are not supported.”

Example: =BYCOL(C1:C3,LAMBDA(col,col))

  • LAMBDA: This function lets you create and return a custom function with a set of names and a formula_expression that uses them.
  • MAP: This function maps each value in the given arrays to a new value.
  • REDUCE: This function reduces an array to an accumulated result.
  • BYROW: This function groups an array by rows.
  • SCAN: This function scans an array and produces intermediate values.
  • MAKEARRAY: This function creates a calculated array of specified dimensions.
  • Create & use named functions: This function lets you create and store custom functions, similar to LAMBDA.