In this juicy tutorial, get ready to uncover the magic of the BYROW function in Google Sheets. Brace yourself as we unlock the full potential of this LAMBDA helper function and discover its incredible capabilities.
Introducing the BYROW Function
Imagine a function that allows you to exclude hidden rows or include visible rows in your formulas without the need for a helper column. That’s exactly what the BYROW function brings to the table, revolutionizing your Google Sheets experience.
Before the introduction of BYROW, we had to resort to cumbersome alternatives like MMULT, Database functions, or QUERY to achieve similar results. However, with the BYROW function, we can now harness the power of functions such as SUM, MIN, MAX, AVERAGE, SPARKLINE, SUBTOTAL, and more.
So, how does it work?
BYROW Function Syntax and Arguments
To fully grasp the BYROW function, let’s take a closer look at its syntax and arguments.
BYROW(array_or_range, lambda)array_or_range: The array or range in which we want the row-wise array result. For example, B3:D5.lambda: A LAMBDA for a single row, usually the first row, to calculate a single result. The BYROW function will expand it in the givenarray_or_range.
In simple terms, BYROW takes an array or range and applies a lambda function to each row, providing a neat array result.
Now that we understand the syntax, let’s dive into the practical aspects.
How to Use the BYROW Function in Google Sheets
Let’s bring this theory into practice with a step-by-step guide on how to use the BYROW function in Google Sheets.
-
Start by entering your desired formula in a specific cell. For instance, we can utilize the SUM function in cell F3 by typing
=sum(B3:D3). -
To convert this formula into a standalone LAMBDA function, use the following syntax:
=LAMBDA(r,SUM(r))(B3:D3). Note that the highlighted section represents thefunction_call, which is only required for standalone LAMBDA use. -
In the BYROW function, omit the
function_call. Your final formula in cell F3 would look like this:=BYROW(B3:D5,LAMBDA(r,SUM(r))).
By following these straightforward steps, you are now proficient in utilizing the BYROW function to its fullest potential.
Handling Open Range and Zeros
When using the BYROW function for an open range, you might encounter undesired zeros in the output column. Thankfully, we have a solution.
To tackle open ranges and zeros, you can employ the IF function along with the BYROW formula. Here’s an example:
=ArrayFormula(if(A3:A="",,BYROW(B3:D,LAMBDA(r,SUM(r)))))The IF function checks whether there are values in column A, and if true, returns the output of the BYROW formula. This approach eliminates the need for an extra column.
If you prefer a more comprehensive solution that removes all zeros, regardless of their placement, you can utilize the LET function:
=ArrayFormula(LET(result,BYROW(B3:D,LAMBDA(r,SUM(r))),IF(result=0,if(,,),result)))By employing the LET function, you have complete control over the elimination of zeros in your data.
BYROW Function to Include Visible Rows (Exclude Hidden Rows) in Aggregation
Let’s uncover an exciting feature of the BYROW function that enables us to include only visible rows and exclude hidden rows in our aggregations.
Say goodbye to painstaking manual filtering or grouping! With the BYROW function combined with SUBTOTAL, we can effortlessly handle hidden rows. Here’s how:
=BYROW(A2:A,LAMBDA(range,SUBTOTAL(103,range)))In this example, the BYROW formula returns 1 in non-blank rows and 0 in blank rows. If you hide any rows, the corresponding cell’s value will be 0, even if you can’t see it.
To make use of this in a practical scenario, we can incorporate it into a COUNTIFS formula. For instance:
=countifs(A2:A,"Apple",BYROW(A2:A,LAMBDA(range,SUBTOTAL(103,range))),">0")By applying the BYROW formula within COUNTIFS, we can ensure that only visible rows are included in our aggregations.
Unlock the Full Potential of BYROW in Google Sheets
The BYROW function in Google Sheets is a game-changer, providing us with newfound capabilities and flexibility. Its ability to handle hidden rows, exclude zeros, and expand non-expanding formulas elevates our spreadsheet experience.
Embrace the power of BYROW and unleash the full potential of Google Sheets. For more invaluable tips and tricks, visit Crawlan.com, your go-to resource for all things Google Sheets.
So, what are you waiting for? It’s time to revolutionize your spreadsheet game and take it to new heights with the remarkable BYROW function. Happy Sheets-ing!
