In Google Sheets, when you write a complex formula that needs to process a row of data—such as concatenating a first name and last name, checking if a value exceeds a budget, and outputting a specific status—you usually write it in one cell and drag it down 1,000 rows. As previously covered, the ARRAYFORMULA function fixes this by automatically applying the logic to the entire column.
However, ARRAYFORMULA has a massive, fatal flaw: it is fundamentally incompatible with certain advanced functions like TEXTJOIN, JOIN, INDEX, and SUM. If you try to wrap an ARRAYFORMULA around a SUM(A2:C2) to add up test scores across three columns for every student, it completely fails. It just sums the entire database into one massive, useless number.
To solve this, Google introduced the incredibly powerful BYROW function, which is designed to iterate a custom LAMBDA formula over a range, row by row, flawlessly bypassing the limitations of ARRAYFORMULA.
The Scenario
Let’s say you have three columns of test scores for 500 students:
- Column A: Student Name
- Column B: Test 1 Score
- Column C: Test 2 Score
- Column D: Test 3 Score
We want Column E to automatically calculate the total sum of all three tests for every student, without dragging any formulas down, and without breaking.
Step 1: Understanding the BYROW Syntax
The BYROW function takes two arguments: the entire data range you want to process, and a LAMBDA function that tells it what math to perform on each individual row.
=BYROW(range, LAMBDA(row_variable, formula))Step 2: Writing the Formula
- Click on cell E2 (the top of your Total Score column).
- Start by defining the range of data we need to sum (Columns B through D):
=BYROW(B2:D, - Next, we open the LAMBDA function and declare a variable name. We can call it
current_row. This variable represents the specific row (e.g., B2:D2) the formula is currently looking at:LAMBDA(current_row, - Finally, we write the math we want to perform using that variable. We want to sum it up:
SUM(current_row))
Step 3: The Complete Execution
Close all the parentheses. Your final, complete formula in cell E2 should look exactly like this:
=BYROW(B2:D, LAMBDA(current_row, SUM(current_row)))Press Enter.
Google Sheets will instantly take row 2 (B2:D2), pass it into the LAMBDA, sum it up, and print the result. It will then automatically step down to row 3 (B3:D3), sum it up, and print it. It repeats this infinitely down the entire spreadsheet. You have successfully created an array-based calculation using a function (SUM) that traditionally refuses to work inside arrays.
Handling Blank Rows (The Pro Tip)
Just like standard arrays, BYROW will continue running all the way down into your empty, blank rows, leaving you with hundreds of useless 0 values at the bottom of Column E.
To fix this, wrap the math inside an IF statement that checks if the row is blank:
=BYROW(B2:D, LAMBDA(current_row, IF(COUNTA(current_row)=0, "", SUM(current_row))))Now, the column will remain perfectly pristine and empty until you actually type a test score into Columns B, C, or D.