Most Excel users know SUMPRODUCT as the function that multiplies two columns together and sums the result. That is its most basic use — and it barely scratches the surface. SUMPRODUCT is one of the most versatile formulas in Excel, capable of conditional counting, weighted averages, OR logic, case-sensitive sums, and more — all without helper columns, without Ctrl+Shift+Enter, and in every Excel version. This guide shows you seven powerful uses that will change how you approach data analysis.
SUMPRODUCT Syntax and How It Works
SUMPRODUCT takes one or more arrays, multiplies their corresponding elements row by row, then sums all the results into a single number:
| Argument | Required? | What it means |
|---|---|---|
| array1 | Required | First range or array. If the only argument, SUMPRODUCT simply sums all values in it. |
| array2, array3... | Optional | Additional ranges. Each must be the same size as array1. Up to 255 arrays in Excel 2007 onwards. |
The key insight is that SUMPRODUCT handles arrays natively — no need for Ctrl+Shift+Enter. Logical tests inside it return TRUE/FALSE, which SUMPRODUCT automatically converts to 1/0 when you use multiplication. This is the foundation of every advanced use below.
-- converts TRUE/FALSE to 1/0 explicitly: --( A:A="North"). Multiplication with * does the same conversion implicitly: (A:A="North")*(B:B="Done"). Both work — multiplication is more common for multi-condition formulas.
Use 1 — Conditional Sum with Multiple Criteria (AND Logic)
SUMPRODUCT can do everything SUMIFS does — and more. Multiply conditions inside it to apply AND logic: only rows where all conditions are TRUE contribute to the sum.
Use 2 — Conditional Count with Multiple Criteria
Remove the sum column and SUMPRODUCT becomes a counter — identical to COUNTIFS but more flexible. Multiply your conditions together; the result is the number of rows where every condition is TRUE.
Use 3 — OR Logic (Sum or Count Where Any Condition Matches)
SUMIFS uses AND logic only. Getting OR logic requires multiple SUMIFS and addition. SUMPRODUCT handles OR logic elegantly by adding conditions instead of multiplying — but you must wrap the OR portion to prevent double-counting rows that satisfy multiple conditions.
Use 4 — Weighted Average
A weighted average assigns different importance to different values. SUMPRODUCT is the cleanest way to calculate one — multiply each value by its weight, sum the products, then divide by the total weight.
Use 5 — Sum by Month or Year (Without Helper Columns)
SUMIFS cannot use the MONTH() or YEAR() functions as criteria — it only accepts direct comparisons. SUMPRODUCT has no such restriction. Wrap MONTH() or YEAR() inside it to sum values for a specific time period directly from a date column.
">="&date — it cannot call MONTH() or YEAR() as a condition. SUMPRODUCT accepts any formula that returns an array, making it ideal for time-based grouping without adding helper columns to your data.
Use 6 — Case-Sensitive Sum or Count
Every Excel comparison — SUMIFS, COUNTIFS, VLOOKUP — is case-insensitive. "apple", "Apple", and "APPLE" all match equally. When your data has mixed-case codes that must be distinguished, combine SUMPRODUCT with the EXACT function to force a case-sensitive match.
Use 7 — Count Unique Values in a Range
Before the UNIQUE function arrived in Excel 365, SUMPRODUCT was the only way to count distinct values in a range using a single formula. It still works in all Excel versions and remains the right choice when you need a distinct count on a filtered or conditional subset.
Common SUMPRODUCT Errors and How to Fix Them
#VALUE! error
The arrays in your formula are not the same size. SUMPRODUCT requires every range to have exactly the same number of rows and columns. Check that A2:A100 and B2:B100 are both 99 rows — even one row of difference causes #VALUE!.
Result is zero when you expect a non-zero number
Most commonly caused by a text vs number mismatch. If your criteria column contains numbers stored as text, the comparison (A:A=1000) returns FALSE for every row. Use VALUE(A:A) or convert the column to numbers. Also check for trailing spaces in text values.
Formula is very slow on large datasets
Never use full column references like A:A in SUMPRODUCT — it processes all 1,048,576 rows. Always use a defined range like A2:A10000. Also, SUMPRODUCT is slower than SUMIFS for simple criteria — only use it when SUMIFS cannot handle your requirement.
=SUMPRODUCT(A:A, B:B) multiplies over a million cells on every calculation. This can make your workbook noticeably slow. Always specify exact row boundaries: =SUMPRODUCT(A2:A1000, B2:B1000).
Frequently Asked Questions
-
What does SUMPRODUCT do in Excel?SUMPRODUCT multiplies corresponding elements in two or more arrays row by row and then sums all the results into a single number. Beyond this basic operation, it handles arrays natively — meaning you can embed logical tests, date functions, and other formulas inside it to perform conditional sums, counts, weighted averages, and more without Ctrl+Shift+Enter.
-
What is the difference between SUMPRODUCT and SUMIFS?SUMIFS is simpler to write for straightforward criteria and is faster on large datasets for single-column sums. SUMPRODUCT is more flexible — it supports OR logic, accepts functions like MONTH(), YEAR(), LEN(), and EXACT() as criteria, can calculate weighted averages, and count distinct values. Use SUMIFS when it covers your needs; use SUMPRODUCT when it does not.
-
What does -- (double negative) do in SUMPRODUCT?The double negative converts TRUE and FALSE values into 1 and 0. A logical test like (A:A="North") creates an array of TRUE/FALSE. SUMPRODUCT needs numbers to multiply and sum, so converting TRUE to 1 and FALSE to 0 makes the math work. Multiplying arrays together does this conversion automatically. The double negative is mainly needed when you have a single condition with no multiplication to trigger the conversion.
-
Why is my SUMPRODUCT returning zero?The most common causes are: numbers stored as text in one of the arrays (so comparisons return FALSE), a data type mismatch between the criteria and the column, trailing spaces in cell values, or arrays of different sizes. Test your condition separately in a blank cell — for example, type =(A2="North") — to verify it returns TRUE where expected.
-
Can SUMPRODUCT do OR logic?Yes. Add conditions together instead of multiplying: =SUMPRODUCT(((A:A="North")+(A:A="South")) * C:C). Addition gives 0 (no match), 1 (one condition matches), or 2 (both match). Since any non-zero value means the row qualifies, multiplying by a value column sums correctly for OR. Use MIN(1,...) around the OR portion if a single row could satisfy both conditions at once to prevent double-counting.
-
Does SUMPRODUCT work in all Excel versions?Yes. SUMPRODUCT works in every version of Excel from Excel 2003 through Microsoft 365. This is one of its key advantages over dynamic array functions like FILTER and UNIQUE, which require Excel 365 or 2021. All seven uses shown in this guide work in any Excel version without array entry (Ctrl+Shift+Enter).