Sum column D where region is East, product is Widget, and quantity in C is greater than 0

公式

=SUMPRODUCT((A2:A500="East")*(B2:B500="Widget")*(C2:C500>0), D2:D500)

说明

Each comparison produces 1 or 0. Multiplying them AND-joins the tests. SUMPRODUCT then multiplies by the amount column and adds up.

步骤

  1. Boolean arrays coerce to 1/0 inside SUMPRODUCT.
  2. All three tests must be true for a row to contribute.
  3. The last argument is the numeric range to sum.

变体

OR across two products

Adding two product tests implements OR.

=SUMPRODUCT((A2:A500="East")*((B2:B500="Widget")+(B2:B500="Gadget")), D2:D500)

Prefer SUMIFS when you can

Faster on large sheets if every test is a simple comparison.

=SUMIFS(D2:D500, A2:A500, "East", B2:B500, "Widget", C2:C500, ">0")

Weighted average

Quantity × price / total quantity.

=SUMPRODUCT(C2:C500, D2:D500)/SUMIF(C2:C500, ">0", C2:C500)