Summarize Each Row or Column (BYROW / BYCOL)

Excel 365LAMBDA

BYROW and BYCOL collapse a 2-D range into one summary value per row (or per column) — a total for each row, a max for each column — spilling the results in a single formula instead of one SUM per row.

Quick formula: to get a total for each row of B2:D10:

=BYROW(B2:D10, LAMBDA(row, SUM(row)))

BYROW hands each row to the LAMBDA as row; here it returns the SUM, spilling one total per row.

Functions used (tap for the full reference guide):

BYROW BYCOL LAMBDA

The example

A row total for each region, from one formula.

	A	B	C	D
1	Region	Jan	Feb	Total
2	West	120	200	320
3	East	90	70	160
4	South	60	110	170

The formula

One formula spills a total per row:

=BYROW(B2:C4, LAMBDA(row, SUM(row))) // 320, 160, 170

How it works

BYROW applies a row-summarizing LAMBDA to each row:

The first argument is the 2-D range (B2:C4).
The LAMBDA(row, …) receives one row at a time as an array and must return a single value — SUM(row), MAX(row), etc.
BYROW spills one result per row, lining up beside your data.
BYCOL does the same down each column — a total or average per column.

Why not just SUM each row? You can — but BYROW is one formula that auto-resizes with the data and shines when the per-row logic is more than a SUM (e.g. MAX(row)-MIN(row) for the spread of each row).

Try it: interactive demo

Live demo

Pick a per-row summary for the table {120,200},{90,70},{60,110}.

For each row, compute

Spills:

Variations

BYCOL for column summaries

A total per column:

=BYCOL(B2:D10, LAMBDA(col, SUM(col)))

Per-row spread

Max minus min of each row:

=BYROW(B2:D10, LAMBDA(r, MAX(r) - MIN(r)))

Per-row condition

Does any cell in the row exceed 100?

=BYROW(B2:D10, LAMBDA(r, OR(r>100)))

Pitfalls & errors

The LAMBDA must return one value per row. If it returns an array, BYROW errors with #CALC!. Aggregate the row to a single result.

BYROW can’t do cross-row logic. Each row is handled independently. For running totals across rows, use SCAN.

Excel 365 only. Use a SUM per row (filled down) in older versions.

Practice workbook

📊

Download the free Summarize Each Row or Column (BYROW / BYCOL) practice workbook

BYROW/BYCOL sum, max, average and spread examples (results shown), plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I total each row with one formula in Excel?

Use =BYROW(range, LAMBDA(row, SUM(row))). It hands each row to the LAMBDA and spills one total per row. Requires Excel 365.

What's the difference between BYROW and BYCOL?

BYROW summarizes each row into one value; BYCOL summarizes each column. Both take a LAMBDA that must return a single value per row or column.

Why does BYROW return #CALC!?

The LAMBDA returned an array instead of a single value. Make sure it aggregates the row to one result, like SUM(row) or MAX(row).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: BYROW · BYCOL · LAMBDA