Excel SUMPRODUCT Function
Chala mitrano — today SUMPRODUCT. Line totals without a helper column — qty times price, then add.
चला मित्रांनो — आज SUMPRODUCT. Helper column शिवाय line total — qty × price, मग बेरीज.
चलो दोस्तों — आज SUMPRODUCT. Helper column के बिना line total — qty × price, फिर जोड़.
Purpose
Multiply corresponding arrays and return the sum of those products.
Return value
The sum of the products.
Syntax
Arguments
| Argument | Required? | Description |
|---|---|---|
array1 | Required | First range or array of numbers. |
array2,… | Optional | More arrays of the same size; Excel multiplies cell-by-cell then sums. |
Version
All Excel versions. LibreOffice Calc too.
How to use — Why → What → How
Why: Invoice lines need qty × rate summed. SUMPRODUCT does both steps in one formula. Qty × price lines in Surat textile and Lyon boutiques. Mixed ₹ and €. In Indian back offices and foreign sister teams, SUMPRODUCT shows up on totals, rates and pack sheets every week.
What: SUMPRODUCT multiplies values in the same position across arrays, then adds those products. Arrays must be the same size. Read every argument in the table once before you type; each one changes the answer on purpose.
How — numbered steps:
- Type =SUMPRODUCT(A2:A10,B2:B10).
- Keep ranges the same height.
- Boolean tests coerce with -- or *1 when you build conditions.
Ravindra Bagale's Tip
Friends, after SUMPRODUCT, find one column or range in a real workbook this week where this lesson saves time.
Ravindra Bagale's Tip – मराठी
मित्रांनो, SUMPRODUCT नंतर या आठवड्यात खरी workbook मधील एक संख्या column सुधारा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, SUMPRODUCT के बाद इस हफ़्ते असली workbook का एक संख्या column सुधारो.
Ravindra Bagale's Tip
Friends, sleep on SUMPRODUCT; rebuild example 1 tomorrow without looking.
Ravindra Bagale's Tip – मराठी
मित्रांनो, SUMPRODUCT वर झोपा; उद्या उदाहरण 1 न पाहता लिहा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, SUMPRODUCT पर सोकर कल उदाहरण 1 बिना देखे लिखो.
Ravindra Bagale's Tip
Friends, say the business question in plain words before =SUMPRODUCT.
Ravindra Bagale's Tip – मराठी
मित्रांनो, =SUMPRODUCT आधी व्यवसाय प्रश्न साध्या शब्दांत सांगा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, =SUMPRODUCT से पहले व्यवसाय प्रश्न सादे शब्दों में बोलो.
Ravindra Bagale's Tip
Friends, check a known case with a calculator before you trust a long SUMPRODUCT chain.
Ravindra Bagale's Tip – मराठी
मित्रांनो, लांब SUMPRODUCT chain वर विश्वास ठेवण्यापूर्वी कॅल्क्युलेटरने एक ज्ञात case तपासा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, लंबी SUMPRODUCT chain पर भरोसा करने से पहले कैलकुलेटर से एक जाना हुआ case जाँचो.
Ravindra Bagale's Tip
Friends, after you finish SUMPRODUCT, open yesterday’s real workbook and find one place this function would have saved ten minutes.
Ravindra Bagale's Tip – मराठी
मित्रांनो, SUMPRODUCT नंतर कालची खरी वर्कबुक उघडा आणि शोधा जिथे हे फंक्शन दहा मिनिटे वाचवू शकले असते.
Ravindra Bagale's Tip – हिंदी
दोस्तों, SUMPRODUCT के बाद कल की असली वर्कबुक खोलो और ढूँढो जहाँ यह फ़ंक्शन दस मिनट बचा सकता था.
Ravindra Bagale's Tip
Friends, treat SUMPRODUCT like a seatbelt on number columns — check a known case first.
Ravindra Bagale's Tip – मराठी
मित्रांनो, SUMPRODUCT ला संख्या columns वरील seatbelt समजा — आधी ज्ञात case तपासा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, SUMPRODUCT को संख्या columns पर seatbelt समझो — पहले जाना हुआ case जाँचो.
Download practice file: practice-sumproduct.xlsx (includes sheet About this data, practice sheets, and Answers).
Worked examples
1) Qty × ₹
Surat textile.
| Qty | Rate ₹ |
|---|---|
| 2 | 100 |
| 3 | 50 |
| 1 | 200 |
Result: 550
What it means: 2×100 + 3×50 + 1×200 = 550.
2) Lyon €
Boutique.
| Qty | € |
|---|---|
| 1 | 40 |
| 2 | 15 |
Result: 70
What it means: 40 + 30 = €70.
3) Single array
One array sums numbers.
| n |
|---|
| 2 |
| 3 |
| 4 |
Result: 9
What it means: With one array, SUMPRODUCT acts like SUM of numbers.
4) Three arrays
Qty × rate × tax factor.
| Qty | Rate | Tax |
|---|---|---|
| 2 | 100 | 1.1 |
| 1 | 50 | 1.1 |
Result: 275.0
What it means: 2×100×1.1 + 1×50×1.1 = 275.
5) Weighted score
Nashik test weights.
| Score | Weight |
|---|---|
| 80 | 0.5 |
| 60 | 0.5 |
Result: 70.0
What it means: Weighted mean numerator when weights sum to 1.
6) Size match
Same height required.
| Qty | Rate |
|---|---|
| 2 | 10 |
| 3 | 20 |
Result: 80
What it means: 2×10 + 3×20 = 80. Different heights → #VALUE!.
Common errors and fixes
Notes and tips
- Revisit the SUMPRODUCT errors table after the quiz.
- Build example 1 from memory on a blank sheet.
- Check number vs text if a result looks wrong.
- Teach one tip in Marathi or Hindi this week.
- Keep currency symbols consistent inside one report column (practice mixes ₹ $ £ €).
- Drill: invoice qty×rate with SUMPRODUCT from memory.
- SUMPRODUCT vs SUMIFS: array products versus criteria totals.
- Fictional sample data only — names and amounts are for learning.
- Print the SUMPRODUCT revision card for one week of real worksheets.
- Ask juniors to predict before they press Enter.
- Track your top mistake with this function monthly.
- Qty × price lines in Surat textile and Lyon boutiques. Mixed ₹ and €.
- Interview tip: explain the business question before the formula.
- Classroom drill: invent three brand-new formulas from your city without the page open.
- If a result looks wrong, check blanks vs zeros before rewriting.
- When Excel versions differ, note the LibreOffice / Sheets fallback in a cell comment.
- Prefer clear ranges over whole-column refs on huge sheets if speed matters.
- Document units (₹, kg, days) in the header so totals stay honest.
- Print the revision card for SUMPRODUCT for one week of real worksheets.
- When helping a junior, ask them to predict before Enter.
- Share one tip with a classmate in Marathi or Hindi.
- Keep currency symbols consistent inside a single report column.
- Interview tip: explain the business question before the formula.
- If a result looks wrong, check blanks vs zeros before rewriting maths.
- When Excel versions differ, write a LibreOffice fallback under a comment.
In real work
Drill: invoice qty×rate with SUMPRODUCT from memory. Qty × price lines in Surat textile and Lyon boutiques. Mixed ₹ and €. Classroom drill: write three brand-new SUMPRODUCT formulas on a blank sheet using names from your own city — if you can invent them without the page open, you own the skill. Then break one formula on purpose and watch the error; recovering from that break is half of interview readiness. Always verify with a known case before trusting a long chain. Keep a personal notebook of the one mistake you make most often with this function; review it monthly.
How it compares
SUMPRODUCT vs SUMIFS: array products versus criteria totals.
Interview corner
- Why not helper column?
- One formula.
- #VALUE!?
- Size mismatch.
- vs SUMIFS?
- Conditions vs raw products.
Related functions
Practice tasks (10)
- Task 1. Qty×rate total.SUMPRODUCT.
- Task 2. € boutique lines.Two arrays.
- Task 3. Three-array tax factor.Three.
- Task 4. Weighted scores.Weights.
- Task 5. Explain size rule.Interview.
- Task 6. Exact ranges not A:A.Perf.
- Task 7. Doc units.Note.
- Task 8. Teach.Teach.
- Task 9. Mismatch error demo.#VALUE!.
- Task 10. Bonus SUMIFS compare.Related.
Quick quiz
Answers: Q1=80, Q2=Same, Q3=Like SUM of numbers, Q4=Can be slow, Q5=SUMIFS / FILTER (check after you try).
Revision card
Standalone card (no tip or photo strip). QR opens this lesson. Download the PNG and revise offline.
