What is the BYROW function?

The BYROW function applies a custom formula row by row across a given range.

=BYROW(array, lambda)
  • array — The range of rows to process
  • lambda — A LAMBDA function defining the calculation for each row

Example 1: Sum Each Row

Dataset (B2:E4):

Product Jan Feb Mar Apr
A 100 150 130 120
B 90 110 140 130
C 200 180 170 190

Formula:

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

Result:

  • Product A → 500
  • Product B → 470
  • Product C → 740

Example 2: Average per Row

=BYROW(B2:E4, LAMBDA(row, AVERAGE(row)))

Result:

  • Product A → 125
  • Product B → 117.5
  • Product C → 185

Advanced Example: Conditional Row Calculation

Count how many months a product exceeded 120 units:

=BYROW(B2:E4, LAMBDA(row, SUM(--(row>120))))

Result:

  • Product A → 2 months
  • Product B → 2 months
  • Product C → 4 months

Practical Use Cases

Use Case Example
Finance Row-wise net profit calculation across accounts
HR Average hours worked per employee across weeks
Sales Total monthly sales per product without helper columns
Education Average scores per student across subjects
Operations Sum or max of daily output for each machine

Benefits of Using BYROW

  • Dynamic array output for each row
  • Cleaner worksheets without helper columns
  • Flexible calculations using LAMBDA functions
  • Works seamlessly with SUM, AVERAGE, MAX, MIN, COUNT, and logical operations

VBA Equivalent

Sub BYROW_Example()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim r As Range
    Dim result As Double
    Dim i As Long
    For i = 2 To 4
        result = Application.WorksheetFunction.Sum(ws.Range("B" & i & ":E" & i))
        ws.Range("F" & i).Value = result
    Next i
End Sub

Conclusion

The BYROW function allows dynamic row-wise calculations, reducing clutter and simplifying complex Excel sheets. Perfect for finance, HR, sales, education, and operations dashboards.