BYROW Function in Google Sheets – LAMBDA Helper Function

Lambda Functions 10-Day Challenge💡 Learn more
Learn more about working with Lambda Functions, Named Functions, and X-Functions in the FREE Lambda Functions 10-Day Challenge course

The BYROW function in Google Sheets operates on an array or range and returns a new column array, created by grouping each row to a single value.

The value for each row is obtained by applying a lambda function on that row.

For example, this BYROW function calculates the average score of all three rows in the input array:

=BYROW(A2:D4,LAMBDA(row, AVERAGE(row)))

Which looks like this in our Google Sheet:

byrow Function In Google Sheets

(Of course, you could use three separate AVERAGE functions to perform this calculation.)

But the important thing here is how the BYROW operates. It’s much more like programming.

We pass an array of data to the BYROW function.

It then passes each row into a lamba function to calculate a single value for that row (the average in this example). The BYROW formula returns these values in a column array, with the same number of rows as the original input array.

🔗 Get this example and others in the template at the bottom of this article.

BYROW Function Syntax

=BYROW(array_or_range, lambda)

It takes two arguments:

array_or_range

This is the input array or range you want to group by rows.

lambda

This is the custom lamba function that is applied to each row. It must have exactly one name argument and one function expression. The name argument resolves to the current row.

BYROW Function Notes

The BYROW formula, given an input array with M rows and N columns, will return a column array with M rows and 1 column.

BYROW Function Example

BYROW can be used with the SPARKLINE formula to create a column of sparklines:

=BYROW(A2:F4,LAMBDA(row, SPARKLINE(row,{"charttype","column"})))

The output of this function is as follows:

byrow With Sparklines

As with the previous average example, you could do this with the sparkline formula on its own, or even with an array formula version of the sparkline.

But it’s a different way of thinking with BYROW. You’re looping over the rows of data and passing the row into a function that operates on each row.

Using The BYROW Function With The FILTER Function

The BYROW function (and BYCOL function) work nicely with the FILTER function. It lets us perform calculations to use as filters without requiring a helper row.

We can set up the BYROW to return an array of TRUE/FALSE values, that we pass into the FILTER function as a filter condition.

Consider this dataset:

byrow Average Data

We want to filter on rows where the salesperson’s monthly average is greater than 50. Oh, and we’re not allowed to use a helper column.

Enter this BYROW formula as the first step:

=BYROW(B2:E4,LAMBDA(row, AVERAGE(ROW)>50))

Which creates an output array where each row is now grouped down to a single boolean value:

TRUE
TRUE
FALSE

(I.e. the rows for Doug and Sarah have averages > 50, but the row for Jenny does not.)

We can then pass this output into a regular FILTER function to display the names:

=FILTER(A2:E4,BYROW(B2:E4,LAMBDA(row, AVERAGE(ROW)>50)))

Byrow Function With Filter Example

BYROW Function Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings.

In this case, right-click the link to open it in an Incognito window to view it.

See Also

See the LAMBDA function and other helper functions MAP, REDUCE, MAKEARRAY, etc. here:

Named Functions and New Functions In Google Sheets For 2022

11 thoughts on “BYROW Function in Google Sheets – LAMBDA Helper Function”

  1. I’m not sure if it’s just because these functions are not fully rolled out for me yet, but pasting your example formula, or trying to use one of my own with what should be a valid lambda(), gives #N/A error and the message “Argument must be a lambda.” Any idea what’s going on?

    Reply

  2. A great way to use the BYROW function is to create subtotals with conditions that are sensitive to filters.

    Example:
    =SUMPRODUCT((BYROW($E$10:$E,LAMBDA(row,SUBTOTAL(109,row))))*($D$10:$D=$B5))

    $E$10:$E is the column to sum in a table of values
    $D$10:$D is a column in the table that contains values to filter on
    $B5 contains the criteria

    Reply

    1. I’m trying to figure out this example you provided but I can’t imagine what the input data looks like by your example. How do I create this to subtotal with conditions?

      Reply

  3. I am trying to use BYROW() to be able to reference the immediately preceding row, but I can’t seem to make it work.
    Here is a quick explanation of the use case as it relates to my formula:
    C2:C is a list of categories only listed for visual purposes as a “Header” for each section. However, I need to build helper columns so that I can use lookups on the data. I want the sheet to look like this.
    X_X
    X__
    X__
    Y_Y
    Y__
    Y__
    Z_Z
    Z etc.
    Here is what I have tried so far:
    In A1 =B1
    In B2 I have tried the following:
    =BYROW(B2:B,LAMBDA(cat,IF(ISBLANK(cat),A1:A,cat)))
    and
    =BYROW(B2:B,LAMBDA(cat,IF(ISBLANK(cat),ARRAYFORMULA(A1:A),cat)))
    and
    =BYROW(C2:C,LAMBDA(cat,IF(ISBLANK(cat),ARRAYFORMULA(INDIRECT(ADDRESS(ROW()-1,COLUMN()))),cat)))
    Any thoughts on this? Thanks
    and

    Reply

  4. how to start the byrow function from the first row not from the second row.

    can we do this like
    if(row()=1,”Byrow Column”,byrow(array,lambda())

    Reply

  5. The BYROW LAMDA function made an easy solution, rather than an ARRAYFORMULA & MMULT, to create sums of rows (which I never got to work). How can I suppress zero results – blank cell rather than “0”?

    Alternatively, could I check ISBLANK on a column not in the summed array?

    Cell E4: =BYROW(I4:AE,LAMBDA(row,SUM(row)))
    replaced: =IF( ISBLANK( A4),, SUM( I4:AE4)) copied sown 1,500 rows

    Also, could BYROW (or ARRAYFORMULA) be employed to replace the following:
    =if(isblank(A4),,-SUMIFS(SB!E$5:E,SB!A$5:A, “<="&A4, SB!C$5:C, "Buy")-SUMIFS(SB!E$5:E,SB!A$5:A, "<="&A4, SB!C$5:C, "Sell")) copied down 1500 rows?

    Much thanks

    Reply

  6. The BYROW / BYCOL functions do not seem to work together with VSTACK / HSTACK functions, does anybody know if this is a known issue?

    Example:
    =VSTACK(BYCOL(A1:A3,LAMBDA(col,IFERROR(INDIRECT(“‘”&col&”‘!C:C”)))))

    Expectation:
    The ranges generated by BYCOL are vertically stacked and result in a range consisting of only one column (that can be used e.g. in a FILTER function).

    Actual result:
    The VSTACK does nothing at all, the resulting range is simply the three ranges horizontally stacked.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *