How to use SUMPRODUCT with criteria in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT((A2:A8=G1)*C2:C8*D2:D8)Multiply a TRUE/FALSE test into the product: =SUMPRODUCT((A2:A8=G1)*C2:C8*D2:D8).
- Result on the example
- $1,117.50 in
G2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
A2:A8=G1 is TRUE on the paint rows and FALSE elsewhere. In multiplication TRUE counts as 1 and FALSE as 0, so the other categories contribute nothing and SUMPRODUCT adds quantity × price for paint only.
=SUMPRODUCT((A2:A8=G1)*C2:C8*D2:D8)Result1117.5
A2:A87 rows × 1 col: "Paint", "Tools", "Paint", "Paint", …A range: 7 cells in one column.
G1"Paint"The value in G1.
A2:A8=G1{7 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …The = operator tests whether the two sides are equal.
C2:C87 rows × 1 col: 12, 5, 8, 6, …A range: 7 cells in one column.
A2:A8=G1)*C2:C8{7 rows × 1 col} 12, 0, 8, 6, …The * operator multiplies.
D2:D87 rows × 1 col: $38.50, $19.99, $44.00, $27.25, …A range: 7 cells in one column.
A2:A8=G1)*C2:C8*D2:D8{7 rows × 1 col} 462, 0, 352, 163.5, …The * operator multiplies.
SUMPRODUCT((A2:A8=G1)*C2:C8*D2:D8)1117.5SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
A2:A8=G1)*C2:C8*D2:D8{7 rows × 1 col} 462, 0, 352, 163.5, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT((A2:A8=G1)*C2:C8*D2:D8). We ran it in Google Sheets mode on the same data and it returns $1,117.50.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(--(A2:A8=G1),C2:C8,D2:D8) | $1,117.50 | The comma form: -- turns TRUE/FALSE into 1/0, and text in the number columns is treated as 0 instead of causing #VALUE!. |
| =SUM(FILTER(C2:C8*D2:D8,A2:A8=G1))Excel only | $1,117.50 | Excel 365: FILTER keeps the paint line totals and SUM adds them. |
Common mistakes
- In the comma form, the test must be converted:
=SUMPRODUCT((A2:A8=G1),C2:C8,D2:D8)treats TRUE/FALSE as 0 and returns 0. Add--. - Every range must be the same size, including the test.
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.
Use SUMPRODUCT with criteria
G2, calculate the revenue (quantity × price) for the category in G1 only.Questions
How do I add a second condition?
Multiply another test: =SUMPRODUCT((A2:A8=G1)*(C2:C8>5)*C2:C8*D2:D8).
How do I use OR in SUMPRODUCT?
Add the tests and keep results above 0: ((A2:A8="Paint")+(A2:A8="Tools")>0).
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.