How to SUMIF across multiple columns in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT((B2:B8=I1)*C2:F8)SUMIF adds only one column, so use SUMPRODUCT: =SUMPRODUCT((B2:B8=H1)*C2:F8) totals every column for the rows that match.
- Result on the example
- 3517 in
I2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in I2. Change any input and the result updates, or edit the formula to experiment.
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.
=SUMPRODUCT((B2:B8=I1)*C2:F8)Result3517
B2:B87 rows × 1 col: "Coffee", "Coffee", "Tea", "Bakery", …A range: 7 cells in one column.
I1"Coffee"The value in I1.
B2:B8=I1{7 rows × 1 col} TRUE, TRUE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
C2:F87 rows × 4 cols: 412, 398, 435, 451, …A range: 7 rows × 4 columns.
B2:B8=I1)*C2:F8{7 rows × 4 cols} 412, 398, 435, 451, …The * operator multiplies.
SUMPRODUCT((B2:B8=I1)*C2:F8)3517SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
B2:B8=I1)*C2:F8{7 rows × 4 cols} 412, 398, 435, 451, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT((B2:B8=I1)*C2:F8). We ran it in Google Sheets mode on the same data and it returns 3517.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(FILTER(C2:F8,B2:B8=I1)) | 3517 | 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) | 3517 | 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).
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.
SUMIF across multiple columns
I2, total all four weeks of units for the category in I1.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.
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.