How to sum values that match a row label and a column header in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT((B2:B7=I1)*(C1:F1=I2)*C2:F7)Multiply a test on the row labels by a test on the headers: =SUMPRODUCT((B2:B7=I1)*(C1:F1=I2)*C2:F7).
- Result on the example
- $25,930 in
I3 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in I3. Change any input and the result updates, or edit the formula to experiment.
How it works
B2:B7=I1 is a column of TRUE/FALSE (one per rep) and C1:F1=I2 is a row of TRUE/FALSE (one per month). Multiplying them by the grid keeps only the cells where both are TRUE: the West reps in March. SUMPRODUCT adds them.
=SUMPRODUCT((B2:B7=I1)*(C1:F1=I2)*C2:F7)Result25930
B2:B76 rows × 1 col: "West", "East", "West", "North", …A range: 6 cells in one column.
I1"West"The value in I1.
B2:B7=I1{6 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
C1:F11 row × 4 cols: "Jan", "Feb", "Mar", "Apr"A range: 4 cells in one row.
I2"Mar"The value in I2.
C1:F1=I2{1 row × 4 cols} FALSE, FALSE, TRUE, FALSEThe = operator tests whether the two sides are equal.
B2:B7=I1)*(C1:F1=I2{6 rows × 4 cols} 0, 0, 1, 0, …The * operator multiplies.
C2:F76 rows × 4 cols: $8,200, $7,650, $9,100, $8,840, …A range: 6 rows × 4 columns.
B2:B7=I1)*(C1:F1=I2)*C2:F7{6 rows × 4 cols} 0, 0, 9100, 0, …The * operator multiplies.
SUMPRODUCT((B2:B7=I1)*(C1:F1=I2)*C2:F7)25930SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
B2:B7=I1)*(C1:F1=I2)*C2:F7{6 rows × 4 cols} 0, 0, 9100, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT((B2:B7=I1)*(C1:F1=I2)*C2:F7). We ran it in Google Sheets mode on the same data and it returns $25,930.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMIFS(XLOOKUP(I2,C1:F1,C2:F7),B2:B7,I1) | $25,930 | XLOOKUP picks the month column and SUMIFS applies the region. Faster on large grids. |
| =SUMIFS(INDEX(C2:F7,0,MATCH(I2,C1:F1,0)),B2:B7,I1) | $25,930 | The same in versions without XLOOKUP. |
Common mistakes
- The header test must be a row and the label test a column. Two row-shaped or two column-shaped tests multiply into the wrong shape and give #VALUE! or a wrong total.
- SUMIFS can't take a two-dimensional sum range with a one-dimensional condition, so
=SUMIFS(C2:F7,B2:B7,I1)returns #VALUE!. Pick the column first.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Sum values that match a row label and a column header
I3, total the sales for the region in I1 in the month in I2.Questions
How do I sum a range of months for a region?
Test the headers against a list: =SUMPRODUCT((B2:B7=I1)*ISNUMBER(MATCH(C1:F1,{"Jan","Feb","Mar"},0))*C2:F7).
Does this work in Google Sheets?
Yes, SUMPRODUCT and the SUMIFS/XLOOKUP versions work the same way.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.