SUMPRODUCT in Excel: 5 Practical Examples
Raja Misal House has five order lines with Qty and Price. To get total revenue he normally adds a column =C2*D2, copies it down and sums it. SUMPRODUCT does the multiply-and-add in one cell: =SUMPRODUCT(C2:C6,D2:D6) gives ₹9,850.
SUMPRODUCT multiplies matching items in two or more ranges and adds the results. Conditions written as (B2:B6="Pune") become 1 or 0, so they switch rows on or off.
Download the practice file (sumproduct-examples.xlsx)
Excel file, 6 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.
Quick answer
- Put the quantities in C2:C6 and prices in D2:D6.
- Type
=SUMPRODUCT(C2:C6,D2:D6). - Press Enter: 9850.
- Add a condition:
=SUMPRODUCT((B2:B6="Pune")*C2:C6*D2:D6)gives 6300. - All ranges must be the same size.
=SUMPRODUCT(C2:C6,D2:D6)
=SUMPRODUCT((B2:B6="Pune")*C2:C6*D2:D6)
The order lines
| Item | City | Qty | Price | Qty × Price |
|---|---|---|---|---|
| Misal Pav | Pune | 40 | 90 | 3,600 |
| Vada Pav | Pune | 60 | 25 | 1,500 |
| Misal Pav | Nashik | 30 | 85 | 2,550 |
| Poha | Kolhapur | 25 | 40 | 1,000 |
| Tea | Pune | 80 | 15 | 1,200 |
Five examples
| Question | Formula | Answer |
|---|---|---|
| 1. Total revenue | =SUMPRODUCT(C2:C6,D2:D6) | 9,850 |
| 2. Revenue in Pune | =SUMPRODUCT((B2:B6="Pune")*C2:C6*D2:D6) | 6,300 |
| 3. Revenue in Pune OR Nashik | =SUMPRODUCT(((B2:B6="Pune")+(B2:B6="Nashik"))*C2:C6*D2:D6) | 8,850 |
| 4. Plates of Misal Pav sold | =SUMPRODUCT((A2:A6="Misal Pav")*C2:C6) | 70 |
| 5. Lines with Qty above 30 in Pune | =SUMPRODUCT((B2:B6="Pune")*(C2:C6>30)) | 3 |
How the conditions work
B2:B6="Pune"gives TRUE, TRUE, FALSE, FALSE, TRUE.- Multiplying turns TRUE into 1 and FALSE into 0.
- Rows with 0 drop out: 3600 + 1500 + 1200 = 6300.
*between conditions means AND;+between conditions means OR (wrap them in brackets first).
Weighted average
Marks 80, 70 and 90 in B10:B12 with weights 30%, 30% and 40% in C10:C12: =SUMPRODUCT(B10:B12,C10:C12)/SUM(C10:C12) gives 81. A plain AVERAGE would give 80 and ignore the weights.
SUMPRODUCT or SUMIFS?
For simple AND conditions SUMIFS is faster and easier to read. Use SUMPRODUCT when you need to multiply columns, use OR logic, or test something SUMIFS cannot, like MONTH(A2:A6)=9.
Ravindra Bagale's Tip
If SUMPRODUCT gives #VALUE!, check that every range has the same number of rows. C2:C6 with D2:D7 is the most common cause.
Ravindra Bagale's Tip – मराठी
SUMPRODUCT #VALUE! देत असेल तर सगळ्या ranges मध्ये rows सारख्याच आहेत का ते बघा. C2:C6 आणि D2:D7 हेच सगळ्यात common कारण असतं.
Ravindra Bagale's Tip – हिंदी
SUMPRODUCT #VALUE! दे तो देखिए सारी ranges में rows बराबर हैं या नहीं। C2:C6 और D2:D7 ही सबसे common वजह होती है।
Practice task
- Open the Orders sheet. Check G2 shows 9850.
- Change the Tea price to 20. Total becomes 10250 and Pune becomes 6700.
- Change it back and check the OR example (8850).
- Write a formula for Misal Pav revenue only. Expect 6150.
- Check the weighted average in G10 (81).
Learn it properly
Course lessons:
Related guides: SUMIF and SUMIFS · COUNTIF and COUNTIFS · Percentage Formulas in Excel: Growth, Share, Discount, Margin and GST
Frequently asked questions
What does SUMPRODUCT do?
It multiplies matching items in the ranges and adds all the products.
Do I need Ctrl + Shift + Enter for SUMPRODUCT?
No. SUMPRODUCT handles arrays by itself in every Excel version.
How do I write OR in SUMPRODUCT?
Add the conditions: ((B2:B6="Pune")+(B2:B6="Nashik")).
Why does it return 0?
Text numbers or a misspelt condition. Check one row by hand.