When analyzing project data or multi-column reports in Google Sheets, you often run into a scenario where criteria exist both vertically (down rows) and horizontally (across column headers). Standard functions like SUMIF or SUMIFS are great for single-column criteria, but they struggle natively when you need a dynamic two-way lookup.

If your sheet has repeated blocks like Project Name and Hours across columns, and task names down rows, this guide covers the most robust and elegant formulas to sum values matching both a dynamic column header and a row value.

The Scenario

Suppose you have the following layout spanning columns A:F and rows 1:4:

|       A       |   B   |       C       |   D   |       E       |   F   |
1 | Project A     | Hours | Project B     | Hours | Project C     | Hours |
2 | Set up        | 4     | Set up        | 2     | Pre meet.     | 1     |
3 | Design        | 10    | Set up        | 20    | Design.       | 15    |
4 | Design        | 2     | Testing.      | 10    | Build.        | 20    |

You want to calculate: Total hours for "Project A" where the task is "Design" (expected result: 10 + 2 = 12).

Method 1: The Best Approach (SUMIF + INDEX & MATCH)

The cleanest and most reliable way to solve this in Google Sheets is pairing SUMIF with dynamic ranges generated by INDEX and MATCH.

The Formula

=SUMIF(
  INDEX(A2:F4, 0, MATCH("Project A", A1:F1, 0)),
  "Design",
  INDEX(A2:F4, 0, MATCH("Project A", A1:F1, 0) + 1)
)

How It Works

  • MATCH("Project A", A1:F1, 0) finds the column index where "Project A" resides (which evaluates to column 1).
  • INDEX(A2:F4, 0, MATCH(...)) returns the entire task column for that project. Setting the row argument to 0 tells INDEX to return all rows in that column.
  • MATCH(...) + 1 targets the column immediately to the right, which contains the corresponding hours.
  • SUMIF(...) checks the returned task column for "Design" and adds up the matching values from the hours column.

Tip: Replace "Project A" and "Design" with cell references (e.g., H1 and H2) so you can change inputs without editing the formula.

Method 2: Using SUMPRODUCT (Offset Array Technique)

If you prefer an array-based calculation that avoids multiple INDEX calls, you can use SUMPRODUCT. This approach evaluates the entire range at once by shifting the sum range by one column.

The Formula

=SUMPRODUCT(
  (A1:E1 = "Project A") * 
  (A2:E4 = "Design") * 
  (B2:F4)
)

Why This Works

  • A1:E1 = "Project A" creates a horizontal mask of TRUE/FALSE across project header columns.
  • A2:E4 = "Design" flags every task cell that matches "Design".
  • B2:F4 is the hours range, intentionally shifted one column to the right. When the matrix multiplication occurs, only the values corresponding to both true conditions are summed.

Method 3: Dynamic Multi-Column Stacking (LAMBDA + REDUCE)

If you plan to scale this sheet or build reports across many tasks and projects automatically, you can restructure (unpivot) your data on the fly using modern Google Sheets functions like REDUCE and VSTACK:

=QUERY(
  REDUCE({"Project","Task","Hours"}, SEQUENCE(COLUMNS(A1:F1)/2, 1, 1, 2), 
    LAMBDA(acc, col, 
      VSTACK(acc, 
        MAP(INDEX(A2:F4, , col), INDEX(A2:F4, , col+1), 
          LAMBDA(task, hrs, {INDEX(A1:F1, 1, col), task, hrs})
        )
      )
    )
  ),
  "SELECT SUM(Col3) WHERE Col1 = 'Project A' AND Col2 = 'Design' LABEL SUM(Col3) ''",
  1
)

While powerful, this method is significantly more complex and is usually only necessary when building automated dashboard summaries.

Best Practice Recommendation

For standard day-to-day spreadsheet tracking, Method 1 (SUMIF + INDEX/MATCH) is the clear winner:

  • It is fast and non-volatile.
  • It handles blank rows gracefully.
  • It is easy for other team members to read and maintain.