# How to use SUMPRODUCT with criteria in Excel

Multiply a TRUE/FALSE test into the product: `=SUMPRODUCT((A2:A8=G1)*C2:C8*D2:D8)`.

**Excel:** `=SUMPRODUCT((A2:A8=G1)*C2:C8*D2:D8)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Category | Item | Qty | Price |  | Category | Paint |
| Paint | Interior white 1 gal | 12 | 38.5 |  | Revenue |  |
| Tools | Roller kit | 5 | 19.99 |  |  |  |
| Paint | Exterior gray 1 gal | 8 | 44 |  |  |  |
| Paint | Primer 1 gal | 6 | 27.25 |  |  |  |
| Tools | Angled brush | 15 | 9.5 |  |  |  |
| Supplies | Drop cloth | 10 | 12.75 |  |  |  |
| Paint | Ceiling white 1 gal | 4 | 35 |  |  |  |

Formula in G2: `=SUMPRODUCT((A2:A8=G1)*C2:C8*D2:D8)`

Result: $1,117.50

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

## Other ways

- `=SUMPRODUCT(--(A2:A8=G1),C2:C8,D2:D8)`: 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 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.

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

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/sumproduct-with-criteria