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
In SUMPRODUCT, all ranges must be the same size – if you give C2:C11 and G2:G12, you get #VALUE!. Many students give a whole column (C:C) and the file becomes slow. Keep the ranges exact or use Table columns. And when you use + for OR, check that a single row doesn't match both conditions.
Ravindra Bagale's Tip – मराठी
SUMPRODUCT मध्ये सगळ्या ranges ची size same पाहिजे – C2:C11 आणि G2:G12 दिलं तर #VALUE! येतो. बरेच students पूर्ण column (C:C) देतात आणि file slow होते. Ranges exact ठेवा किंवा Table columns वापरा. आणि OR साठी + वापरल्यावर एकाच row ला दोन्ही conditions लागू होत नाहीत ना, हे check करा.
Ravindra Bagale's Tip – हिंदी
SUMPRODUCT में सारी ranges का size same होना चाहिए – C2:C11 और G2:G12 दिया तो #VALUE! आता है. बहुत से students पूरा column (C:C) दे देते हैं और file slow हो जाती है. Ranges exact रखो या Table columns इस्तेमाल करो. और OR के लिए + लगाने पर check करो कि एक ही row पर दोनों conditions लागू न हों.
Practice task
With SUMPRODUCT find: Blinkit Fruits sales, number of orders with Amount > ₹200 and Mins ≤ 12, and the weighted average price per unit.