3.16 SUMPRODUCT
=SUMPRODUCT(array1, [array2], …) multiplies matching items and adds them. It works in every version and handles conditions without Ctrl + Shift + Enter.
| Goal | Formula | Result |
|---|---|---|
| Pune quantity | =SUMPRODUCT((C2:C11="Pune")*F2:F11) |
10 |
| Pune OR Nashik sales | =SUMPRODUCT(((C2:C11="Pune")+(C2:C11="Nashik"))*G2:G11) |
904 |
| Weighted average delivery time (weights = Qty) | =SUMPRODUCT(H2:H11,F2:F11)/SUM(F2:F11) |
11.88 |
| Sales in November (without helper column) | =SUMPRODUCT((MONTH(B2:B11)=11)*G2:G11) |
2,870 |
* between conditions means AND, + means OR. TRUE/FALSE become 1/0 when multiplied.
Ravindra Bagale's Tip
SUMPRODUCT madhe saglya ranges chi size same pahije – C2:C11 aani G2:G12 dila tar #VALUE! yeto. Khup students poora column (C:C) detat aani file slow hote. Ranges exact theva kiwa Table columns vapra. Ani OR sathi + vaparlyavar ekach row don conditions na lagu hot nahi na, he check kara.
Practice task
With SUMPRODUCT find: Blinkit Fruits sales, number of orders with Amount > ₹200 and Mins ≤ 12, and the weighted average price per unit.