BYROW function

This function groups an array by rows by application of a LAMBDA function to each row.

Sample Usage

BYROW(A1:C3,LAMBDA(row,MAX(row)))

BYROW(A1:C3,LAMBDA(row,SUM(row)))

Syntax

BYROW(array_or_range,LAMBDA)

  • array_or_range: The array or range to group by rows.
  • LAMBDA: A LAMBDA that’s applied to each row 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 row 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 row in the input array.
  • Every row 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

Sum row-wise

Example Data:

 

A

B

C

1

3

5

7

2

4

3

5

3

1

2

4

Example: =BYROW(A1:C3,LAMBDA(row,SUM(row)))

Result:

15

12

7

Filters years where any quarterly sales exceeded 1300

Example data:

 

A

B

C

D

E

1

 

Q1

Q2

Q3

Q4

2

2019

1060

295

1425

280

3

2020

270

585

675

170

4

2021

285

1200

780

1235

5

2022

1440

1390

45

650

Example:=FILTER(A2:A5,BYROW(B2:E5,LAMBDA(row,MAX(row)>1300)))

Result:

2019

2022

Returns a 3x1 array with the difference between max and min value of each row using a named function as LAMBDA

Make a Copy

Example data:

 

A

B

C

1

3

5

7

2

4

3

5

3

1

2

4

Example: =BYROW(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(row)-MIN(row), where row is an argument placeholder defined for MAX_MIN_DIFF.

Result:

4

2

3

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: =BYROW(C1:C4,LAMBDA(a,b,a+b))

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

The last parameter of BYROW wasn’t a LAMBDA

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

“Argument must be a LAMBDA.”

Example: =BYROW(C1:C4,4)

The LAMBDA passed to BYROW 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: =BYROW(C1:C4,LAMBDA(C1, C1+1))

For 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 row to a single value

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

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

Example: =BYROW(C1:E1,LAMBDA(row,row))

Related functions

  • LAMBDA function: This function lets you create and return a custom function with a set of names and a formula_expression that uses them.
  • MAP function: This function maps each value in the given arrays to a new value.
  • REDUCE function: This function reduces an array to an accumulated result.
  • BYCOL function: This function groups an array by columns.
  • SCAN function: This function scans an array and produces intermediate values.
  • MAKEARRAY function: 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.
true
Visit the Learning Center

Using Google products, like Google Docs, at work or school? Try powerful tips, tutorials, and templates. Learn to work on Office files without installing Office, create dynamic project plans and team calendars, auto-organize your inbox, and more.

Search
Clear search
Close search
Google apps
Main menu
443373508636615938
true
Search Help Center
true
true
true
true
true
35
false
false