# How to sum values that match a row label and a column header in Excel

Multiply a test on the row labels by a test on the headers: `=SUMPRODUCT((B2:B7=I1)*(C1:F1=I2)*C2:F7)`.

**Excel:** `=SUMPRODUCT((B2:B7=I1)*(C1:F1=I2)*C2:F7)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H | I |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Rep | Region | Jan | Feb | Mar | Apr |  | Region | West |
| Ana | West | 8200 | 7650 | 9100 | 8840 |  | Month | Mar |
| Ben | East | 6900 | 7320 | 6750 | 7480 |  | Total |  |
| Cara | West | 9450 | 8870 | 9620 | 10150 |  |  |  |
| Dev | North | 5600 | 6050 | 5890 | 6210 |  |  |  |
| Eli | East | 7780 | 8010 | 8460 | 7990 |  |  |  |
| Fay | West | 6340 | 6980 | 7210 | 7550 |  |  |  |

Formula in I3: `=SUMPRODUCT((B2:B7=I1)*(C1:F1=I2)*C2:F7)`

Result: $25,930

## 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.

## Other ways

- `=SUMIFS(XLOOKUP(I2,C1:F1,C2:F7),B2:B7,I1)`: 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)`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-matching-a-row-and-column-header