The direct answer: use =SUMPRODUCT((range1=criteria)*(range2)) to sum values in range2 where range1 meets the criteria. For example, =SUMPRODUCT((A2:A10="North")*(B2:B10)) sums all amounts in B2:B10 where the region in A2:A10 is “North”. This works because the comparison returns TRUE/FALSE arrays, which Excel coerces to 1s and 0s when multiplied.
What SUMPRODUCT Actually Does
SUMPRODUCT multiplies corresponding components in given arrays and returns the sum of those products. Its syntax is SUMPRODUCT(array1, [array2], ...). When you give it a single array, it simply sums the values—like a built-in SUM. But its real power comes from using arrays of TRUE/FALSE values (from comparisons) and numeric values. Excel treats TRUE as 1 and FALSE as 0 in arithmetic operations, so multiplying a condition array by a value array zeroes out the values that don’t meet the condition.
How to Use SUMPRODUCT with a Single Criterion
Suppose you have a sales table: products in A2:A20, regions in B2:B20, and sales amounts in C2:C20. To sum sales for the “East” region, enter:
=SUMPRODUCT((B2:B20="East")*(C2:C20))
This returns the total of C2:C20 where B2:B20 equals “East”. The multiplication (B2:B20="East")*(C2:C20) creates an array of amounts for matching rows and 0 for non-matching rows, then SUMPRODUCT sums that array.
Using Multiple Criteria with SUMPRODUCT
You can add more conditions by multiplying more arrays. For instance, to sum sales for “East” region and product “Widget”, use:
=SUMPRODUCT((B2:B20="East")*(A2:A20="Widget")*(C2:C20))
Each condition returns an array of 1s and 0s; multiplying them together gives 1 only where all conditions are TRUE. You can also use OR logic by adding conditions, but be careful with parentheses: =SUMPRODUCT(((B2:B20="East")+(B2:B20="West"))*(C2:C20)) sums sales for either East or West.
SUMPRODUCT vs. SUMIFS and Other Functions
SUMIFS is usually simpler for straightforward criteria: =SUMIFS(C2:C20, B2:B20, "East", A2:A20, "Widget"). However, SUMPRODUCT is more flexible. It can handle:
- Criteria based on other calculations, like
=SUMPRODUCT((C2:C20>100)*(C2:C20))to sum values over 100. - Array operations that SUMIFS cannot do, such as summing the product of two columns with a condition:
=SUMPRODUCT((B2:B20="East")*(C2:C20*D2:D20)). - Counting with multiple criteria:
=SUMPRODUCT((B2:B20="East")*(A2:A20="Widget"))returns the count of rows meeting both.
SUMPRODUCT also works with wildcards? No—SUMPRODUCT does not support wildcards like * or ?. For partial matches, use SUMIFS with wildcards or use ISNUMBER(SEARCH()) inside SUMPRODUCT.
Common Errors and How to Fix Them
One frequent issue is the #VALUE! error when ranges have different sizes. Ensure all arrays have the same number of rows (and columns if using 2D). Another issue is using entire columns (e.g., A:A) which can slow down calculation; limit ranges to your data. Also, if you have text numbers in the value range, SUMPRODUCT may treat them as 0; convert them to real numbers.
Another common mistake is forgetting to coerce TRUE/FALSE to numbers. In older Excel versions, you might need to use double unary operator --: =SUMPRODUCT(--(B2:B20="East"), C2:C20). In modern Excel, multiplication works fine, but using the double unary is still a good habit.
Performance and Large Data Sets
SUMPRODUCT is an array formula that calculates each element in the range. On large datasets (tens of thousands of rows), it can slow down your workbook because it processes every cell. For better performance, consider using SUMIFS which is optimized for simple criteria, or use Excel Tables and structured references. If you must use SUMPRODUCT on a huge range, avoid entire column references and keep ranges tight.
Alternatives to SUMPRODUCT for Conditional Sums
Besides SUMIFS, you can use:
- SUMIF for a single criterion:
=SUMIF(B2:B20, "East", C2:C20). - FILTER function (Excel 365) to return an array and then SUM:
=SUM(FILTER(C2:C20, B2:B20="East")). - PivotTables for interactive summaries without formulas.
- Power Query for complex data transformations before summarization.
Each has its place. SUMPRODUCT shines when you need to multiply arrays or use conditions that involve other calculations, like weighted averages: =SUMPRODUCT(B2:B10, C2:C10)/SUM(C2:C10).
Frequently Asked Questions
Can SUMPRODUCT handle OR conditions?
Yes, by adding condition arrays. For example, to sum sales for “East” or “West”, use =SUMPRODUCT(((B2:B20="East")+(B2:B20="West"))*(C2:C20)). The addition creates an array of 1s and 2s; any non-zero multiplies to the value. Be careful not to double-count if both conditions could be true for the same row (which is impossible here, but in general use OR logic carefully).
Why does my SUMPRODUCT return 0?
Check that your criteria text matches exactly (including spaces) and that the ranges are the same size. Also, if your value range contains text or blanks, SUMPRODUCT may ignore them or treat as 0. Use =SUMPRODUCT((B2:B20="East")*(C2:C20)) and press Ctrl+Shift+Enter if you are using an older Excel version (though modern Excel handles it as a dynamic array).
Can I use wildcards in SUMPRODUCT?
No, SUMPRODUCT does not support wildcards. For partial text matches, use =SUMPRODUCT((ISNUMBER(SEARCH("North", A2:A20)))*(B2:B20)). SEARCH returns a number if found, otherwise an error; ISNUMBER converts that to TRUE/FALSE.
Is SUMPRODUCT the same as an array formula?
It’s an array formula under the hood, but you don’t need to press Ctrl+Shift+Enter in modern Excel. It evaluates arrays internally. In older versions, you could use SUMPRODUCT to avoid CSE array entry, which was a common reason to use it.
How do I sum only if a cell is not blank?
Use =SUMPRODUCT((A2:A20<>"")*(B2:B20)). The condition A2:A20<>"" returns TRUE for non-blank cells. Note that cells with formulas that return “” are considered non-blank; to exclude those, use =SUMPRODUCT((A2:A20<>"")*(ISNUMBER(A2:A20))*(B2:B20)).
Can SUMPRODUCT work with dates?
Yes, dates are stored as numbers. For example, to sum sales after a certain date, use =SUMPRODUCT((A2:A20>DATE(2024,1,1))*(B2:B20)). You can also reference a cell containing a date.
SUMPRODUCT is a versatile function that goes beyond simple sums. With a clear understanding of how it multiplies arrays and coerces TRUE/FALSE, you can handle complex conditional sums, counts, and weighted calculations. For everyday single-criterion sums, SUMIFS is often simpler, but SUMPRODUCT remains a powerful tool in your Excel arsenal.
Related
- Excel Normalize Text: Clean Data Fast
- Excel Normalize Data Range: 3 Easy Methods
- Excel Name Manager Dynamic Range Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF in Excel for Text: Practical Examples and TipsYes. In Excel, use COUNTIF to count cells that match text: with status values in A2:A20, count cells…
- COUNTIF in Excel: How It Works and How to Use ItExcel’s COUNTIF function counts cells in a range that meet one condition. Give it a range to inspect…
- Sort Excel by Month: Dates, Names, and Custom Lists EasilyTo sort Excel by month, select the full data range and choose Data > Sort. Sort real date…
- Excel IRR Monthly: Formula, Setup, and Common ErrorsTo calculate a monthly internal rate of return in Excel, put cash flows in chronological order at equal…
- Excel COUNTIF Year in Date: Formula & ExamplesTo count cells containing a date in a specific year, use =SUMPRODUCT(--(YEAR(range)=year)) or, in Excel 2021 and Microsoft…
- How to Use VLOOKUP Between Two Worksheets in ExcelTo use VLOOKUP between two sheets in Excel, enter a formula on the sheet where you want the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.