Math
SUMIFS & SUMIF across horizontal columns (multi-row column headers)
Sum wide horizontal budget/actual rows left-to-right where Row 1 Quarter and Row 2 Scenario headers match criteria—without transposing—using horizontal SUMIFS.
SUMIFS works horizontally too—just keep every 1-row range the exact same width!
Many analysts assume SUMIFS only sums vertical columns. In fact, SUMIFS happily sums a 1xN horizontal row (B3:G3) against one or more 1xN horizontal header rows (Quarter in B1:G1, Scenario "Act"/"Bud" in B2:G2)—as long as every range has the EXACT SAME dimensions (1 row by 6 columns)!
That eliminates brittle =B3+D3+F3 manual cell picking across 24-month Actual/Budget FP&A sheets: =SUMIFS(B3:G3, B2:G2, "Act") totals all Actual columns automatically!
This fixed six-column example requires all amounts numeric and both header rows present. Negative adjustments and genuine zeros remain valid. It does not automatically extend its ranges when new periods are added outside B:G.
The criterion <>Q1 means every non-Q1 label, not only Q2 or Q3. New or misspelled labels therefore affect that selection. SUMIFS criteria support wildcard syntax; use an explicit inclusion list when that is what the report needs.
| Row | B (Q1 Act) | C (Q1 Bud) | D (Q2 Act) | E (Q2 Bud) | F (Q3 Act) / G (Q3 Bud) |
|---|---|---|---|---|---|
| Quarter (Row 1) | Q1 | Q1 | Q2 | Q2 | Q3 / Q3 |
| Scenario (Row 2) | Act | Bud | Act | Bud | Act / Bud |
| Revenue (Row 3) | 120 | 110 | 150 | 140 | 180 / 170 |
| Horizontal Rollups | All Act = 450 | All Bud = 420 | Q1 Act = 120 | Act != Q1 = 330 | Zero manual + signs! |
Sum all "Act" columns horizontally across Row 3
Totals B3 + D3 + F3 (120 + 150 + 180 = 450) wherever Row 2 equals "Act".
=IF(OR(COUNT(B3:G3)<>6,COUNTBLANK(B1:G2)>0),"Invalid inputs",SUMIFS(B3:G3, B2:G2, "Act"))Multi-row horizontal SUMIFS (Quarter AND Scenario)
Combines Row 1 Quarter criteria ("<>Q1") with Row 2 Scenario ("Act") for Q2+Q3 Actuals (330).
=IF(OR(COUNT(B3:G3)<>6,COUNTBLANK(B1:G2)>0),"Invalid inputs",SUMIFS(B3:G3, B1:G1, "<>Q1", B2:G2, "Act"))The single-condition SUMIF equivalent
SUMIF puts the criteria range first and the sum range third, whereas SUMIFS puts the sum range first. Both ranges here have one row and six columns.
=IF(OR(COUNT(B3:G3)<>6,COUNTBLANK(B1:G2)>0),"Invalid inputs",SUMIF(B2:G2,"Act",B3:G3))Try the example
Use the original values or change them to test your understanding.