Math & Trig
Sum and find MAX of every Nth horizontal column (repeating Budget vs Actual blocks)
Sum or find the maximum across columns selected by repeated headers, such as Actual and Budget.
Stop clicking =C2+E2+G2+I2+K2... across 24 Budget/Actual columns!
The example selects columns by the header Actual rather than by every Nth physical position. This permits nonuniform layouts as long as the headers and value range remain aligned.
Keep headers accurate and extend the fixed ranges when adding columns. The formulas are not universally insert-proof. Values in the example are in thousands; positive Actual-minus-Budget variance is not automatically favorable for every account type.
| Q1 Budget (B) | Q1 Actual (C) | Q2 Budget (D) | Q2 Actual (E) | Q3 Budget (F) | Q3 Actual (G) |
|---|---|---|---|---|---|
| $100k | $112k | $110k | $98k | $120k | $135k |
| Total Budget = $330k | Total Actual = $345k | Peak Actual = $135k | Total Variance = +$15k | — | — |
Horizontal SUMIFS and MAX(FILTER) across repeating column blocks
Lock $B$1:$G$1 with dollar signs so the formula copies down across dozens of P&L expense lines.
=SUMIFS(B2:G2, $B$1:$G$1, "Actual")Peak Actual period and YTD Actual-minus-Budget variance
Horizontal FILTER isolates only the "Actual" sub-columns before MAX picks the highest period.
Assumes numeric Actual values. With no matching header the formula returns a message, not a false zero peak.
=IF(COUNTIF($B$1:$G$1,"Actual")=0,"No Actual columns",MAX(FILTER(B2:G2, $B$1:$G$1 = "Actual")))Try the example
Use the original values or change them to test your understanding.