BYROW Function

The BYROW function applies a function to each row in array and returns one result per row in a single array. For example, if BYROW is given a range with 10 rows, it returns an array of 10 results, one per row. The calculation performed on each row can be a built-in function like SUM or COUNT, or a custom LAMBDA function that operates on row values.

BYROW is especially useful in three situations:

1. To apply per-row logic that "collapses" an entire row of values into a single result. Anything with a conditional, threshold, or "all/any/none" test (for example, count values per row above 90, or flag rows where every value passes).
2. To generate a "helper column" of values or Booleans that can be used as an input to FILTER, SORTBY, XMATCH, conditional formatting, or another calculation.
3. To perform per-row calculations on an array, not a range. When the input is an array produced by another formula (FILTER, HSTACK, TRIMRANGE, etc.), BYROW gives you a convenient way to iterate through an array of unknown size and generate per-row calculations.

See below for specific examples.

Note: BYROW has two close siblings, BYCOL and MAP. BYCOL does the same thing across columns instead of rows, reducing each column to a single value. MAP applies a calculation to every cell rather than every row or column. Use MAP when you want to transform individual values. Use BYCOL when you want to generate per-column calculations.

Key features

• Returns a single array with one result per row.
• Accepts any calculation that reduces a row to a single value (SUM, AVERAGE, MAX, TEXTJOIN, custom LAMBDA, etc.).
• Supports a concise "eta lambda" syntax for single-argument functions like SUM and MAX.
• The output can feed FILTER, SORTBY, XMATCH, and other native array functions.
• Companion function: BYCOL does the same across columns.
• Available in Excel 365 and Excel 2024.

Table of contents

• Basic usage
• Sum each row
• Count values per row that meet a condition
• Average each row ignoring zeros
• Flag rows with all, any, or none logic
• Remove blank rows with FILTER
• Row-level weighted average
• Sort rows by a calculated value
• Multiple calculations with HSTACK
• Orientation when nesting other functions
• Notes

Basic usage

BYROW takes two arguments: an array to process and a function to run on each row. The function can be written in two ways. The first way is as a long-form custom LAMBDA. This is the general form, and the one you'll see whenever the calculation needs custom logic:

=BYROW(array,LAMBDA(row,SUM(row)))
=BYROW(array,LAMBDA(row,AVERAGE(IF(row<>0,row))))
=BYROW(array,LAMBDA(row,MAX(row)-MIN(row)))

The second way is a short-form "eta lambda" syntax. Instead of wrapping the function in a LAMBDA, you pass just the function name. This works for single-argument functions like SUM, AVERAGE, MIN, MAX, COUNT, and COUNTA:

=BYROW(array,SUM) // sum each row
=BYROW(array,MAX) // max of each row
=BYROW(array,AVERAGE) // average of each row
=BYROW(array,COUNTA) // count of non-empty values

Note that the eta form can't be used when the calculation needs a comparison, a conditional, an extra reference, or any other logic. For those cases, use the long form.

The row name in the LAMBDA is arbitrary. Use any name you like (r, x, values, etc.) as long as you're consistent inside the LAMBDA.

Sum each row

In the worksheet below, the goal is to sum the values in each row of the range C5:H15. The formula in J5 is:

=BYROW(C5:H15,SUM)

BYROW processes all 11 rows at once and returns an array of 11 sums that spill into J5:J15. The same pattern works for any single-argument aggregate:

=BYROW(C5:H15,MAX) // max of each row
=BYROW(C5:H15,MIN) // min of each row
=BYROW(C5:H15,AVERAGE) // average of each row

Tip: If the source data will grow over time, wrap the range with the TRIMRANGE function or use the dot operator so BYROW always gets just the used portion of the range: =BYROW(C5:.H1000,SUM). This makes the range dynamic so that results will expand and contract with the data automatically.

For more details, see Get row totals.

Count values per row that meet a condition

BYROW is useful when you want to count values per row that meet one or more specific conditions. This is where the eta syntax breaks down and the full custom LAMBDA syntax is required. In the worksheet below, the goal is to count the number of values in each row greater than 90. The formula in J5 is:

=BYROW(C5:H15,LAMBDA(row,SUM(--(row>90))))

Working from the inside out, the logical expression row>90 returns an array of TRUE and FALSE values. The double negative (--) coerces those to 1s and 0s, which SUM adds together to return the count. Because the calculation uses a comparison, the eta form won't work. We need a LAMBDA to hold the logical expression.

You might be tempted to use COUNTIF or COUNTIFS here, but the *IFS functions require a range, not an array, so they don't play nicely inside BYROW. As a general rule, when you're working with arrays produced by other formulas, use Boolean logic instead of the *IFS family.

Average each row ignoring zeros

Zeros sometimes mean "no data" rather than "a real value of zero" (a store that wasn't open, a test that wasn't taken, a product that wasn't stocked). Averaging those zeros pulls the result down incorrectly. The BYROW function gives you a clean way to exclude zeros when averaging row values. In the worksheet below, the goal is to calculate the average of each row while ignoring cells that contain 0 values. The formula in J5 is:

=BYROW(C5:G15,LAMBDA(row,AVERAGE(IF(row<>0,row))))

Inside the LAMBDA, the expression row<>0 returns TRUE for non-zero values and FALSE for zeros. The IF function passes only the non-zero values to AVERAGE; zero values become FALSE and are automatically ignored. The result is a per-row average that treats zeros as if they weren't there. For comparison, the formula in K5 does a simple average that includes all values, including zero:

=BYROW(C5:G15,AVERAGE) // formula in K5 includes zero values

Flag rows with all, any, or none logic

One of BYROW's most useful features is "collapsing" an entire row to a single Boolean. This lets you answer questions like "did every value pass?", "did any value hit the threshold?", or "did nothing fall below the limit?" for an entire row of values. In the worksheet below, each row holds five test scores. The formulas in I5, J5, and K5 evaluate each row three different ways:

=BYROW(C5:G15,LAMBDA(row,MIN(row)>=70)) // all passed
=BYROW(C5:G15,LAMBDA(row,MAX(row)>=95)) // any hit 95+
=BYROW(C5:G15,LAMBDA(row,SUM(--(row<60))=0)) // none failed

Each formula reduces a row of five scores to a single TRUE or FALSE. The trick is picking the right aggregate function (MIN, MAX, SUM, etc.) to express the required logic:

• MIN(row)>=70: the smallest value passes, so all values must pass.
• MAX(row)>=95: the largest value hits the threshold, so at least one does.
• SUM(--(row<60))=0: there are zero failing scores, so none failed.

This pattern is the foundation for many of the examples below. Any time you have a row-level rule that reduces to "yes" or "no", BYROW can generate the full column of answers in a single formula, and that column can then drive FILTER, SORTBY, or conditional formatting.

While the worksheet above contains just five columns of data, the same formulas would work just as well with 50 columns.

Remove blank rows with FILTER

An interesting way to use BYROW is to create an input for another function. For example, you can pair BYROW with the FILTER function to solve a problem that's surprisingly tricky in Excel: removing rows where all columns are empty. In the worksheet below, the goal is to return only the rows from B5:E16 that contain data, with other completely blank rows removed. The formula in G5 looks like this:

=FILTER(B5:E16,BYROW(B5:E16,LAMBDA(row,SUM(--(row<>""))>0)))

Working from the inside out, BYROW generates a TRUE/FALSE array (one result per row) indicating which rows have any content. FILTER then uses that array as its include argument and keeps only the rows marked TRUE. The result is the original data with blank rows removed.

This general pattern is quite useful: Use BYROW logic to create the include argument for FILTER. Adjust the LAMBDA to run any row test you like ("at least three non-empty cells", "all values positive", "contains a specific code"), and FILTER will return the matching rows. No helper columns required.

For more details, see Remove blank rows.

Row-level weighted average

BYROW's LAMBDA has access to more than just the row variable; you can reference other ranges or named ranges inside the LAMBDA. This is what makes weighted calculations straightforward. In the worksheet below, each row contains three test scores, and the weights appear in the named range weights (C4:E4). The goal is to calculate a weighted average for each row. The formula in H6 is:

=BYROW(C6:E15,LAMBDA(row,SUMPRODUCT(row,weights)/SUM(weights)))

Inside the LAMBDA, the SUMPRODUCT function multiplies each row's values by the weights and sums the products. Dividing by the sum of weights normalizes the result so the weights don't have to add up to 1. BYROW applies the calculation to all 10 rows in a single formula and spills the weighted averages into H6:H15.

This is a case where BYROW replaces a copied-down helper formula. Without it, you'd write =SUMPRODUCT(C6:E6,weights)/SUM(weights) in H6 and copy down. That's a perfectly valid approach, but with BYROW, the calculation lives in one cell and can be adapted to extend the results dynamically by adding a dot operator like this:

=BYROW(C6:.E100,LAMBDA(row,SUMPRODUCT(row,weights)/SUM(weights)))

For more details on weighted averages, see Weighted average.

Sort rows by a calculated value

In this example, the goal is to sort countries by their total quarterly sales from highest total to lowest. The challenge is that the total doesn't exist in the data. The traditional solution is to use a helper column. BYROW makes a separate helper column unnecessary. The formula in G5 looks like this:

=SORTBY(B5:E15,BYROW(C5:E15,SUM),-1)

BYROW returns a one-column array of row totals, which SORTBY uses as the by_array. The -1 sorts in descending order, so the country with the highest total appears first. The totals themselves never appear in the output; they're calculated on the fly, used only to drive the sort, and discarded.

The same pattern works for any per-row metric you can write:

=SORTBY(data,BYROW(data,LAMBDA(r,MAX(r)-MIN(r))),-1) // sort by volatility
=SORTBY(data,BYROW(data,AVERAGE),1) // sort by row average, ascending

To extract the top n rows by total sales, wrap the result with the TAKE function:

=TAKE(SORTBY(B5:E15,BYROW(C5:E15,SUM),-1),3) // top 3 by total

Multiple calculations with HSTACK

BYROW's function argument accepts a single calculation, but you can stack multiple calculations using the HSTACK function (or VSTACK, depending on the layout you want). In the worksheet below, BYROW is configured to return the sum, max, and min for each row side by side. The formula in J5 is:

=BYROW(C5:H15,HSTACK(SUM,MAX,MIN))

You can swap the functions inside HSTACK for any combination of aggregates. Custom LAMBDAs work here too, as long as each one returns a single value.

This formula is subject to the current array of arrays limitation in Excel. It will initially calculate results for the first row only, and you'll need to copy the formula down manually to fill the rest. This is a known Excel limitation that may be resolved in a future update.

Orientation when nesting other functions

Keep in mind that the row passed into the LAMBDA inside BYROW is a horizontal array (one row of values in columns). If you nest a function inside the LAMBDA that has its own row/column orientation argument, you may need to set the orientation explicitly, or the function might misinterpret the data. A good example is counting unique values per row with UNIQUE and COUNTA:

=BYROW(A1:D3,LAMBDA(row,COUNTA(UNIQUE(row))))   // won't work
=BYROW(A1:D3,LAMBDA(row,COUNTA(UNIQUE(row,1)))) // works

In the first formula, UNIQUE defaults to comparing rows, but row is a single row, so UNIQUE returns the row unchanged, and COUNTA simply counts the four values. In the second formula, UNIQUE's by_col argument is set to TRUE (1), which tells UNIQUE to compare values in columns instead of rows. The second formula will correctly return the count of unique values in each row.

Notes

• BYROW is available in Excel 365 and Excel 2024. Older versions don't support it.
• The calculation inside BYROW should normally reduce each row to a single value. Returning an array per row will trigger the array of arrays limitation (see the HSTACK example).
• BYROW processes all rows at once and returns a single spilled array. To send the output into another function like FILTER or SORTBY, use it directly; there's no need to write results to the sheet first.
• The row variable inside a LAMBDA is itself a one-row array, so it works in functions that accept arrays: SUM, AVERAGE, MAX, TEXTJOIN, MATCH, SUMPRODUCT, and so on.
• BYROW can't see a row's position in the original data. If your calculation needs a row index, use MAKEARRAY or MAP with SEQUENCE instead.
• For column-by-column processing instead of row-by-row, use the BYCOL function.