How to Use the BYROW and BYCOL Functions in Google Sheets to Apply Calculations Across Entire Rows or Columns

When computing summaries across data matrices in Google Sheets—such as row totals for each employee, maximum monthly expenses, or average scores per student—analysts traditionally entered a formula into the first cell and dragged the fill handle down hundreds of rows. While simple, dragging formulas creates bloated files, makes updating logic cumbersome, and breaks whenever new rows are inserted at the base of the table.

To eliminate manual formula copying, Google Sheets introduced BYROW and BYCOL. Working in tandem with LAMBDA expressions, these dynamic array functions iterate across an entire table row-by-row or column-by-column, returning a single, self-updating spill range from just one formula cell. Here is how to master BYROW and BYCOL in Google Sheets.

Understanding BYROW and BYCOL

Both functions belong to Google Sheets’ dynamic array iteration library:

• BYROW: Applies a custom calculation to each horizontal row in an array, outputting a vertical column of results.
• BYCOL: Applies a calculation to each vertical column in an array, outputting a horizontal row of results.
• Zero Dragging: Enter the formula in one single cell; it automatically calculates results for every row in the dataset and expands dynamically.

Function Syntax and Arguments

BYROW Syntax

=BYROW(array, LAMBDA(row, calculation))

• array: The range of cells to process (e.g., B2:E50).
• LAMBDA(row, ...): A custom lambda function where row acts as an alias representing each individual row range.

BYCOL Syntax

=BYCOL(array, LAMBDA(col, calculation))

• array: The target table range.
• LAMBDA(col, ...): A custom lambda where col represents each individual column.

Practical Example 1: Summing Rows Dynamically with BYROW

Suppose cells B2:E20 contain quarterly sales figures across four columns (Q1 through Q4). To calculate the annual total for every row in column F with a single formula:

=BYROW(B2:E20, LAMBDA(r, SUM(r)))

Google Sheets evaluates each row r, executes SUM(r), and spills the totals down column F. If you modify any quarterly value, the summary updates automatically.

Practical Example 2: Finding Maximum Values per Column with BYCOL

To identify the peak value recorded in each quarter across columns B through E:

=BYCOL(B2:E20, LAMBDA(c, MAX(c)))

This single formula outputs a 4-column horizontal array showing the highest figure in each column.

Requirement	Traditional Method	BYROW / BYCOL Method
Formula Maintenance	Copied across hundreds of individual cells	Single formula cell controls the entire column
Handling New Rows	Must drag formulas down manually	Automatically calculates for all rows in range
Calculation Overhead	High workbook calculation bloat	Optimised native array evaluation

Combining BYROW with Logical Conditions

You can nest logic inside the LAMBDA expression. For example, to count how many quarters exceeded a sales quota of 10,000 for each row:

=BYROW(B2:E20, LAMBDA(r, COUNTIF(r, ">10000")))

This delivers immediate conditional insights across your entire dataset without auxiliary helper columns.