# How to SUMIF across multiple columns in Excel

SUMIF adds only one column, so use SUMPRODUCT: `=SUMPRODUCT((B2:B8=H1)*C2:F8)` totals every column for the rows that match.

**Excel:** `=SUMPRODUCT((B2:B8=I1)*C2:F8)` (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 |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Item | Category | Week 1 | Week 2 | Week 3 | Week 4 |  | Category | Coffee |
| Latte | Coffee | 412 | 398 | 435 | 451 |  | Units |  |
| Mocha | Coffee | 186 | 174 | 192 | 205 |  |  |  |
| Chai | Tea | 143 | 151 | 138 | 160 |  |  |  |
| Muffin | Bakery | 220 | 236 | 214 | 241 |  |  |  |
| Espresso | Coffee | 265 | 251 | 278 | 270 |  |  |  |
| Green tea | Tea | 98 | 104 | 91 | 112 |  |  |  |
| Croissant | Bakery | 175 | 168 | 190 | 183 |  |  |  |

Formula in I2: `=SUMPRODUCT((B2:B8=I1)*C2:F8)`

Result: 3517

## How it works

`B2:B8=I1` gives TRUE for each coffee row. Multiplying that column by the four week columns keeps the coffee rows and zeros out the rest, and SUMPRODUCT adds every remaining cell.

## Other ways

- `=SUM(FILTER(C2:F8,B2:B8=I1))`: Excel 365 and Google Sheets: FILTER keeps the coffee rows with all their columns and SUM adds them.
- `=SUMIF(B2:B8,I1,C2:C8)+SUMIF(B2:B8,I1,D2:D8)+SUMIF(B2:B8,I1,E2:E8)+SUMIF(B2:B8,I1,F2:F8)`: One SUMIF per column. Long, but plain and fast in any version.

## Common mistakes

- `=SUMIF(B2:B8,I1,C2:F8)` looks right but adds only Week 1: SUMIF shrinks the sum range to the size of the criteria range.
- If no row matches, the FILTER version returns #CALC! in Excel. Add a fallback: `FILTER(C2:F8,B2:B8=I1,0)`.

## Questions

**How do I add a second condition?**

Multiply another test in: `=SUMPRODUCT((B2:B8=I1)*(A2:A8<>"Mocha")*C2:F8)`.

**How do I total only some of the columns?**

Narrow the range, for example `C2:D8` for weeks 1 and 2.

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/sumif-across-multiple-columns