Sum matching columns across repeating headers (Actual vs Budget)

Eliminate manual =B2+D2+F2+H2 formulas on wide financial sheets

Quarterly budget templates often alternate Q1 Actual, Q1 Budget, Q2 Actual, Q2 Budget across columns B through E. Clicking =B2+D2+... breaks easily when someone misses a column over 12 months.

The example uses exact Actual and Budget labels. A one-row header criterion and one-row sum range handle a single department; use the header mask multiplied across the numeric matrix to aggregate all departments. SUMIF itself is not generally limited to one-dimensional ranges, but mismatched shapes do not express this repeated-header aggregation.

Row-level horizontal SUMIF and 2D matrix SUMPRODUCT

SUMPRODUCT broadcasts the 1x4 header mask ($B$1:$E$1="Actual") across every row of B2:E3 cleanly.

=SUMIF($B$1:$E$1, "Actual", B2:E2)

2D grand total of all matching columns across all rows

Totals Sales + Ops Actuals (120 + 150 + 80 + 95 = 445) in one formula.

=SUMPRODUCT(($B$1:$E$1 = "Actual") * B2:E3)

Inputs and interpretation

Header width must match the numeric data matrix. Literal Actual and wildcard *Actual are different criteria; choose one deliberately. Text or errors in an arithmetic matrix can propagate instead of being ignored. Repeated matching headers are intentionally included; no matching header gives zero.

Further reading

• Microsoft Support — SUMIF function