Math & Trigonometry
Row-Wise and Column-Wise Matrix Summaries and Flags (BYROW & BYCOL Formula Patterns)
Evaluate row-by-row totals, minimum-threshold SLA flags, and column-by-column period peaks across 2D matrices using CHOOSEROWS, CHOOSECOLS, and helper vectors.
Summarizing 2D grids across rows (BYROW) and columns (BYCOL)
Analysts working with multi-period scorecards (such as regional branches across Q1–Q4) frequently need row-level aggregates (annual branch total, or whether every single quarter met a minimum floor) alongside column-level aggregates (company-wide quarterly peak or mean).
While Excel 365 offers BYROW and BYCOL with LAMBDA, slicing individual slices with CHOOSEROWS(matrix, row_num) and CHOOSECOLS(matrix, col_num)—paired with explicit row flag columns—delivers identical results without opaque nested LAMBDA syntax.
| Branch | Q1 | Q2 | Q3 | Q4 | Row Total / Floor >= 110 |
|---|---|---|---|---|---|
| Sofia | 120 | 135 | 140 | 155 | 550 (Pass: TRUE) |
| Berlin | 95 | 115 | 125 | 130 | 465 (Pass: FALSE) |
| Madrid | 110 | 120 | 118 | 142 | 490 (Pass: TRUE) |
| Dublin | 130 | 105 | 132 | 148 | 515 (Pass: FALSE) |
| Col Peak (BYCOL MAX) | 130 | 135 | 140 | 155 | 2 Branches All-Pass |
Row-Slice Total & Column-Slice Peak With CHOOSEROWS / CHOOSECOLS
Extracts row 1 of the 4x4 quarterly matrix B2:E5 and sums all four quarters.
=SUM(CHOOSEROWS(B2:E5, 1))Count Rows Where Every Column Meets Target Floor
Row-level MIN(B2:E2) >= 110 flags in F2:F5 test whether a branch cleared the quarterly floor in all four quarters; COUNTIF(F2:F5, TRUE) counts qualifying branches.
=COUNTIF(F2:F5, TRUE)Try the example
Use the original values or change them to test your understanding.