How to Use the BYROW Function in Google Sheets

Published on

In this tutorial, let’s explore the usage of the BYROW function in Google Sheets, serving as a Lambda Helper Function (LHF). Once you’ve mastered the LAMBDA function, incorporating the BYROW helper function will be a breeze.

How does this LHF prove useful? It introduces new capabilities to Google Sheets. Previously, there were virtually no methods to exclude hidden rows (or include visible rows) in formulas without resorting to a helper column. Now, thanks to the introduction of this function, it’s possible, and I’ll delve into the details later in this tutorial.

For your reference, I’ve provided an example here – “SUMIF Excluding Hidden Rows in Google Sheets [Without Helper Column].” This is achievable because the BYROW function can expand the results of non-expanding formulas such as SUM, MIN, MAX, AVERAGE, SPARKLINE, SUBTOTAL, etc.

Before, we used functions like MMULT, database functions, or QUERY to expand such formula results (not all). Here’s an example where an MMULT replaces SUM:

The MMULT formula in cell F3 can replace the =SUM(B3:D3), =SUM(B4:D4), and =SUM(B5:D5) formulas in F3, F4, and F5.

=MMULT(B3:D5, {1; 1; 1})
MMULT - SUM rows

As an alternative, the BYROW function can be used in this context.

Syntax and Arguments

Syntax:

BYROW(array_or_range, lambda)

Arguments:

  • array_or_range: The array or range to use in a lambda function by row (e.g., B3:D5 [as per the screenshot above]).
  • lambda: A lambda for a single row (e.g., B3:D3 [as per the screenshot above]), usually the first row, to calculate a single result. The BYROW function will expand it in the given array_or_range. It’s important to note that a defined name will be used to represent each element in the array, not B3:D3.

Syntax for LAMBDA:

=LAMBDA(name, formula_expression)(function_call)

Important:

The LAMBDA function can take more than one name argument and function_call, but when used with BYROW, only one name argument should be used, and the function_call should be omitted.

How to Use the BYROW Function in Google Sheets

Please scroll up and refer to the image.

In the image, we have an MMULT function used to calculate the total of each row.

To replace it, you can use the following BYROW formula. Clear cells F3:F5 and enter the following code in cell F3.

=BYROW(B3:D5, LAMBDA(r, SUM(r)))

Here’s how you can convert a SUM formula to a LAMBDA and then use it in a BYROW:

  1. Enter =SUM(B3:D3) in cell F3.
  2. Convert it to a standalone LAMBDA as follows: =LAMBDA(r, SUM(r))(B3:D3).

In the above LAMBDA formula, the highlighted part represents the function_call, which is necessary only when using LAMBDA standalone. However, within BYROW, we must omit that in the LAMBDA part and instead use it as the array_or_range.

So in the BYROW formula, B3:D5 is the array_or_range, and LAMBDA(r, SUM(r)) is the lambda.

The BYROW function expands the LAMBDA coded for the first row to all the rows in the array (array_or_range)

Managing Zeros in BYROW Function Results Due to Open Ranges

If you use the above BYROW formula for the array_or_range B3:D instead of B3:D5, you would observe zeros in the output column for blank rows.

BYROW Function Example - Open Range

To address open ranges similarly to how we handle zeros in regular formulas, you can use the following approach:

=IF(SUM(B3:D3)=0, ,SUM(B3:D3))

The IF function checks if the sum of the row is 0. If true, it returns blank; otherwise, it returns the sum.

However, to improve performance and avoid recalculating the SUM function, you can use LET:

=LET(total, SUM(B3:D3), IF(total=0, ,total))

In this formula, ‘total’ represents the sum formula.

To incorporate this into the BYROW Lambda formula, you can use:

=BYROW(B3:D, LAMBDA(r, LET(total, SUM(r), IF(total=0, ,total))))

This modification will eliminate all zeros, regardless of whether they are in blank rows or in the row where the BYROW formula returns a zero.

BYROW Function to Include Visible Rows (Exclude Hidden Rows) in Aggregation

I breathed a sigh of relief when I discovered that my BYROW formula successfully expanded the SUBTOTAL-based LAMBDA formula.

Do you know the reason?

To the best of my knowledge, SUBTOTAL is the only function capable of excluding hidden rows during its evaluation.

This feature can be leveraged with the BYROW function to include only visible rows (and exclude hidden rows) in aggregation.

In the following example, the COUNTIFS in cell D1 counts only “Apple” in visible rows in column A.

BYROW Function Example - Handling Hidden Rows

To utilize the BYROW function in Google Sheets to handle hidden rows, you can use the formula:

=BYROW(A2:A, LAMBDA(range, SUBTOTAL(103, range)))

(replace A2:A with any non-blank column reference within your range)

If you apply the above BYROW formula in cell E2, the formula will return 1 in all non-blank rows and 0 in blank rows.

Even if you hide any row through grouping, filtering, or manual hiding, the value returned by the above formula in the corresponding cell, though invisible, will be 0.

Therefore, I have employed the above BYROW formula within COUNTIFS in cell D1 to handle hidden rows as follows:

=COUNTIFS(A2:A, "Apple", BYROW(A2:A, LAMBDA(range, SUBTOTAL(103, range))), ">0")

To exclude all hidden rows (hidden through filtering, grouping, or manual hiding), use function number 103 within SUBTOTAL. If you want to only exclude rows hidden through filtering, replace 103 with 3.

Alternatively, you can use 9 or 109, but with numeric columns. Function numbers 103 and 3 work regardless of the column type.

Resources

Want to learn lambda helper functions other than the BYROW function in Google Sheets? Here you go!

  1. How to Use the BYCOL Function in Google Sheets
  2. How to Use the SCAN Function in Google Sheets
  3. How to Use the REDUCE Function in Google Sheets
  4. How to Use the MAP Function in Google Sheets
  5. How to Use the MAKEARRAY Function in Google Sheets
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Compare Two Tables for Differences in Excel

To compare two tables with similar data for differences, you can use the XLOOKUP...

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

How to Retrieve the Last Record in Each Group in Excel

As part of data analysis, you may need to retrieve the last record from...

More like this

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

Filter Data with Multi-Select Drop-Downs in Google Sheets

If you've started using multi-select drop-downs in Google Sheets, you may encounter challenges when...

7 COMMENTS

  1. My initial post may have been a bit unclear, and I apologize for any confusion. Below are the score columns (S1 through S5) and the rank.avg columns (AR1 through AR5) for better readability:

    S1 S2 S3 S4 S5
    10 5 7 6
    7 6 7 8 6
    8 8 6 10
    4 10 7 4
    7 8 5 6

    AR1 AR2 AR3 AR4 AR5
    1 4 2 3
    2.5 4.5 2.5 1 4.5
    2.5 2.5 4 1
    3.5 1 2 3.5
    2.5 2.5 1 5 4

    The desired outcome is a row-by-row comparison of the game scores with the RANK.AVG function. I assume that a separate formula is needed for each column.

    [Sample Sheet URL: removed]

    • Hi Eric,

      Thank you for sharing the sample sheet. Unfortunately, I couldn’t enter my formula as I don’t have editing rights.

      For other readers, please note that the first table in the comment contains sample data, and the second table represents the expected result. It’s essentially a row-by-row RANK.AVG.

      Eric, you need to use a combination of BYCOL and BYROW here. Assuming the sample data is in cells A1:E and the first row contains the field labels, you can use the following BYROW+BYCOL combination in cell F2:

      =IFNA(BYROW(A2:E, LAMBDA(r, BYCOL(r, LAMBDA(c, RANK.AVG(c, r))))))

      I hope that helps.

      Thanks

  2. I’d like to use RANK.AVG in a BYROW function to obtain the rank of a cell’s contents compared only to the other columns in its row. However, I’m struggling to find a method for this.

    The goal is to return the rank of the cell compared to all the cells in the range. I attempted nesting a pair of BYROW functions within a RANK.AVG, but that approach didn’t work either.

    Any brilliant ideas? Thanks.

    • Hi, Ritesh,

      Thanks for letting me know. I’ve already posted LET.

      But the issue is I am updating my old contents priority-wise (there are 1200+), which needs immediate attention due to the new functions.

      I’ll update this post ASAP.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.