3.16 SUMPRODUCT
SUMPRODUCT with example — in 30 seconds
What: SUMPRODUCT multiplies matching items in ranges and adds the results. A TRUE/FALSE test inside it acts like a filter.
Why: It answers questions with conditions and weights in one cell, for example a quantity-weighted average delivery time.
Tiny example: Pune quantities are 2, 1, 5 and 2. =SUMPRODUCT((C2:C11="Pune")*F2:F11) gives 10.
SUMPRODUCT with example — 30 सेकंदात
काय: SUMPRODUCT ranges मधले जुळणारे items गुणतो आणि त्यांची बेरीज करतो. आत TRUE/FALSE test टाकला तर तो filter सारखा काम करतो.
का: Conditions आणि weights असलेले प्रश्न तो एका cell मध्ये सोडवतो, उदा. quantity-weighted सरासरी delivery time.
छोटं उदाहरण: Pune च्या quantities 2, 1, 5 आणि 2 आहेत. =SUMPRODUCT((C2:C11="Pune")*F2:F11) उत्तर 10 देतो.
SUMPRODUCT with example — 30 सेकंड में
क्या: SUMPRODUCT ranges के मेल खाते items को गुणा करके जोड़ता है. अंदर TRUE/FALSE test डालें तो वह filter जैसा काम करता है.
क्यों: Conditions और weights वाले सवाल यह एक cell में हल करता है, जैसे quantity-weighted average delivery time.
छोटा उदाहरण: Pune की quantities 2, 1, 5 और 2 हैं. =SUMPRODUCT((C2:C11="Pune")*F2:F11) जवाब 10 देता है.
First look at the data (before → after):
Expected result — Answers
| Question | Answer |
|---|---|
| Units sold in Pune | 10 |
| Qty-weighted average minutes | 11.88 |
About this data: Teaching sample for a fictional shop “HomeNest Supplies”. Figures and names (e.g. Vikram Rao) are made up for this lesson on sumproduct.
Before table and formula as text
Before — Columns used (all ten rows)
| C: City | F: Qty | H: Mins |
|---|---|---|
| Pune | 2 | 9 |
| Nashik | 3 | 12 |
| Pune | 1 | 14 |
| Nagpur | 2 | 11 |
| Kolhapur | 4 | 16 |
| Pune | 5 | 8 |
| Solapur | 1 | 18 |
| Sambhaji Nagar | 3 | 10 |
| Pune | 2 | 13 |
| Nashik | 1 | 17 |
About this data: Demo table only — not real customer or company data. Built to practise sumproduct.
Formula / click path
=SUMPRODUCT((C2:C11="Pune")*F2:F11)
=SUMPRODUCT(H2:H11,F2:F11)/SUM(F2:F11)
Do it step by step:
- Type
=SUMPRODUCT((C2:C11="Pune")*F2:F11)and press Enter. - The test gives TRUE/FALSE. Multiplying turns them into 1/0, so only Pune rows count.
- Type
=SUMPRODUCT(H2:H11,F2:F11)/SUM(F2:F11)for the weighted average. - Format the second cell with 2 decimals.
- Check: 10 units and 11.88 minutes.
स्टेप बाय स्टेप करून बघा:
=SUMPRODUCT((C2:C11="Pune")*F2:F11)type करा आणि Enter दाबा.- Test मधून TRUE/FALSE येतं. गुणल्यावर ते 1/0 होतं, म्हणून फक्त Pune च्या rows मोजल्या जातात.
- Weighted average साठी
=SUMPRODUCT(H2:H11,F2:F11)/SUM(F2:F11)type करा. - दुसऱ्या cell ला 2 decimals चा format द्या.
- Check करा: 10 units आणि 11.88 मिनिटं.
स्टेप बाय स्टेप करके देखें:
=SUMPRODUCT((C2:C11="Pune")*F2:F11)type करें और Enter दबाएँ.- Test से TRUE/FALSE आता है. गुणा करने पर वह 1/0 बनता है, इसलिए सिर्फ़ Pune की rows गिनी जाती हैं.
- Weighted average के लिए
=SUMPRODUCT(H2:H11,F2:F11)/SUM(F2:F11)type करें. - दूसरी cell को 2 decimals का format दें.
- Check करें: 10 units और 11.88 मिनट.
=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 |
About this data: Made-up rows so you can copy the pattern for sumproduct. Currencies may use ₹, £, € or $.
* 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: FreshGo Fruits sales, number of orders with Amount > ₹200 and Mins ≤ 12, and the weighted average price per unit.
Loading practice tasks...
- Total qty of Pune (Easy): Find the total Qty for Pune.
- Invoice: bill total (Easy): Find the total bill: Invoice!C2:C6 times Invoice!D2:D6.
- Delivered amount (Medium): Find the total amount of Delivered orders with SUMPRODUCT.
- Count with two conditions (Medium): Count Pune orders that are Delivered with SUMPRODUCT.
- Invoice: Dairy total (Medium): Find the bill total of "Dairy" items only.
- Invoice: count rows (Medium): Count Stationery rows where Qty is more than 3.
- FreshGo qty (Hard): Find the total Qty of FreshGo orders.
- Weighted average time (Hard): Find the average Mins weighted by Qty.
- Invoice: Stationery qty (Hard): Find the total Qty of Stationery.
- Invoice: average price (Hard): Find the average price per unit: total bill ÷ total Qty.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
