📖 What is the SUMPRODUCT Function?
SUMPRODUCT is a Math & Trigonometry function that takes two or more arrays, multiplies their corresponding elements, and then sums all the resulting products into a single value. At its core it performs a weighted sum — but its true power lies far beyond that.
Unlike standard array formulas, SUMPRODUCT handles arrays natively — no Ctrl+Shift+Enter required. This makes it the preferred tool for conditional counting, conditional summing, weighted averages, and multi-criteria aggregation directly in a regular formula cell. It works seamlessly in all Excel versions from 2003 onwards.
✖
Array Multiplication
Multiplies element-by-element across arrays
➕
Conditional SUM
Replaces SUMIFS with Boolean logic tricks
🔢
Conditional COUNT
Counts with complex multi-criteria filters
⚙️ Syntax & Arguments
=SUMPRODUCT( array1, [array2], [array3], … )
array1 → Required. First array or range (e.g., B2:B10).
[array2] → Optional. Second array — must be same dimensions as array1.
[array3]… → Optional. Up to 255 arrays. All must match in size.
── How it calculates ────────────────────────────────────────
Step 1: Multiply array1[i] × array2[i] × array3[i] for every row i
Step 2: Sum all those products into one final number
── Common usage patterns ────────────────────────────────────
=SUMPRODUCT(B2:B10, C2:C10) → Classic weighted sum
=SUMPRODUCT((A2:A10="North")*(C2:C10)) → Conditional sum (Boolean trick)
=SUMPRODUCT((A2:A10="N")*(B2:B10="Lp")*1)→ Conditional count (2 criteria)
=SUMPRODUCT(B2:B10*C2:C10/D2:D10) → Inline arithmetic on arrays
Mode 1 — Classic (comma syntax)
=SUMPRODUCT(qty, price)
=SUMPRODUCT(B2:B8, C2:C8)
Arrays separated by commas — Excel multiplies then sums.
Mode 2 — Boolean (asterisk syntax)
=SUMPRODUCT((cond1)*(cond2)*vals)
=SUMPRODUCT((A:A="N")*(B:B>5)*C:C)
TRUE/FALSE coerced to 1/0 — acts as conditional SUMIFS.
ℹ️ Boolean trick explained:
(A2:A10="North") returns an array of TRUE/FALSE.
Multiplying by *1 or another array converts them to 1/0, enabling conditional logic.
All arrays must be the same size — mismatched dimensions return a #VALUE! error.
📊 Example 1 — Weighted Revenue Calculation
Basic Use Case
A retail business wants to compute total revenue by multiplying Quantity Sold × Unit Price for each product — all in one formula without a helper column.
| A — Product | B — Qty Sold | C — Unit Price (₹) | D — Revenue (₹) |
|---|---|---|---|
| Laptop | 12 | 55,000 | 6,60,000 |
| Mobile | 35 | 18,000 | 6,30,000 |
| Tablet | 20 | 32,000 | 6,40,000 |
| Headphones | 60 | 4,500 | 2,70,000 |
| Smart Watch | 18 | 12,000 | 2,16,000 |
| TOTAL REVENUE | ₹ 24,16,000 | ||
How SUMPRODUCT computes this:
(12×55,000) + (35×18,000) + (20×32,000) + (60×4,500) + (18×12,000)= 6,60,000 + 6,30,000 + 6,40,000 + 2,70,000 + 2,16,000 = ₹ 24,16,000
Formula in E2 → =SUMPRODUCT(B2:B6, C2:C6)
✅ Result: ₹ 24,16,000
📊 Example 2 — Multi-Condition Weighted Revenue (Boolean Logic)
Advanced / Practical Use Case
A Sales Head wants the total revenue only for the "North" zone where Qty > 15 — two simultaneous conditions applied using SUMPRODUCT's Boolean asterisk technique, replacing a complex SUMIFS entirely and enabling inline arithmetic.
| A — Zone | B — Product | C — Qty | D — Price (₹) |
|---|---|---|---|
| North | Laptop | 20 | 55,000 |
| South | Mobile | 35 | 18,000 |
| North | Tablet | 10 | 32,000 |
| North | Headphones | 60 | 4,500 |
| West | Laptop | 18 | 55,000 |
| North | Smart Watch | 22 | 12,000 |
| North + Qty > 15 Revenue | ₹ 15,64,000 | ||
Boolean array breakdown:
Zone="North" array → [1, 0, 1, 1, 0, 1]Qty>15 array →
[1, 1, 0, 1, 1, 1]AND (multiply) →
[1, 0, 0, 1, 0, 1] (rows 1, 4, 6 pass both)Revenue × filter →
(20×55,000)+(60×4,500)+(22×12,000) = ₹ 15,64,000
Formula in F2 →
=SUMPRODUCT((A2:A7="North")*(C2:C7>15)*C2:C7*D2:D7)
=SUMPRODUCT((A2:A7="North")*(C2:C7>15)*C2:C7*D2:D7)
✅ Result: ₹ 15,64,000
💡 Key Applications
- Finance Calculating weighted average cost of capital (WACC) — weight each component's cost by its proportion in capital structure in a single cell.
- Retail Computing total inventory value (Qty × Unit Cost) across thousands of SKUs without a helper column — far faster than column-by-column multiplication.
- Sales Conditional revenue totals for specific salesperson + product + region combinations without building pivot tables or using helper columns.
-
MIS
Unique count of items in a list using
=SUMPRODUCT(1/COUNTIF(range,range))— a classic trick to count distinct values without removing duplicates. - HR Computing weighted performance scores — multiply each KPI score by its weightage percentage and sum in one formula for appraisal scorecards.
- Audit Conditional transaction counts — count entries satisfying multiple criteria (date range + amount threshold + status) in a single audit check formula.
- Tax Calculating GST amount by multiplying taxable value × applicable rate for each line item and summing — handles mixed rate invoices in one formula.
⚠️ Common Mistakes to Avoid
1. Mismatched Array Sizes → #VALUE! Error
Writing
=SUMPRODUCT(B2:B10, C2:C8) where arrays differ in row count causes a #VALUE! error immediately. SUMPRODUCT requires all arrays to be identical in dimensions.
✔ Fix: Always verify all arrays span the same rows:
=SUMPRODUCT(B2:B10, C2:C10) — count rows in each range before finalizing.2. Using Entire Columns (A:A) — Severe Performance Hit
Writing
=SUMPRODUCT(A:A="North", B:B) forces Excel to process 10,48,576 rows on every calculation cycle. In large workbooks this freezes Excel for seconds.
✔ Fix: Always restrict to the actual data range:
=SUMPRODUCT((A2:A1000="North")*B2:B1000). Define a named range for cleaner formulas.3. Forgetting to Force Boolean Arrays to Numbers
In some Excel versions,
=SUMPRODUCT((A2:A10="North"), B2:B10) using a comma between a Boolean array and a numeric array can produce unexpected results because TRUE/FALSE aren't automatically coerced.
✔ Fix: Use the asterisk
* to multiply (which forces TRUE→1, FALSE→0): =SUMPRODUCT((A2:A10="North")*B2:B10)4. Text Numbers Cause Silent Miscalculation
If quantity or price columns contain numbers stored as text (common in Tally/ERP exports), SUMPRODUCT silently treats them as 0 and under-reports totals with no error shown.
✔ Fix: Convert text-numbers first using VALUE() or multiply by 1:
=SUMPRODUCT(B2:B10*1, C2:C10*1) to force numeric conversion inline.5. Confusing SUMPRODUCT with SUMIF for Simple Cases
Using SUMPRODUCT's Boolean mode for a simple single-condition sum — e.g.,
=SUMPRODUCT((A:A="North")*B:B) — when a plain =SUMIF(A:A,"North",B:B) would be faster, simpler, and more readable.
✔ Fix: Use SUMIF/SUMIFS for simple 1–2 condition sums. Reserve SUMPRODUCT for weighted sums, unique counts, complex inline arithmetic, or 3+ condition scenarios.