Fixing Excel SUMPRODUCT That Returns Zero With Multiple Condition Arrays
You've written a SUMPRODUCT formula with two or three condition arrays, double-checked the ranges, and the cell shows 0. The data is clearly there. A manual filter confirms matching rows exist. Yet SUMPRODUCT disagrees with all of it.
This usually means one of your condition arrays is silently evaluating to all FALSE — or the numbers you're summing are not actually numbers. This guide walks through every root cause in order of likelihood and gives you a repeatable debugging workflow.
What You'll Learn
- Why
SUMPRODUCTreturns0even when matching rows exist - How mismatched range sizes silently break every condition array
- How to detect and fix text-stored numbers in your data
- How to isolate which condition is failing using intermediate helper arrays
- How to correctly force Boolean arrays to integers so multiplication works
Prerequisites
You should be comfortable writing basic SUMPRODUCT formulas and understand that it multiplies corresponding elements across arrays, then sums the products. You don't need any VBA knowledge. Examples below use Excel 365, but the fixes apply to Excel 2016 and later unless noted.
How SUMPRODUCT Evaluates Multiple Condition Arrays
Before fixing the problem, it helps to picture what Excel is actually doing. When you write:
=SUMPRODUCT((A2:A100="East")*(B2:B100="Q1")*(C2:C100))Excel builds three arrays of the same length. The first two are Boolean arrays — each element is TRUE or FALSE. The third is the numeric values you want to sum. Multiplying a Boolean by a number works only if the Boolean is first coerced into 1 or 0. SUMPRODUCT does this coercion automatically during multiplication, which is why the formula usually works. When it doesn't, something is preventing at least one array from producing any non-zero products.
Cause 1: Mismatched Array Sizes
This is the most common silent killer. If your condition ranges have different row counts, SUMPRODUCT returns 0 without any error message in most Excel versions.
=SUMPRODUCT((A2:A100="East")*(B2:B101="Q1")*(C2:C100))A2:A100 has 99 rows. B2:B101 has 100 rows. Excel cannot align these arrays element-by-element, so the entire product evaluates to zero.
Fix: Select all range references in the formula bar and confirm they share identical start rows, end rows, and whether they're on the same sheet. The fastest way is to press F5, click Special > Current Array, or simply count the row numbers manually in the formula bar.
A reliable pattern is to anchor all ranges to the same size variable and define it once in a named range. If your data table is an Excel Table (created with Ctrl+T), use structured references — they always match in size automatically:
=SUMPRODUCT((Sales[Region]="East")*(Sales[Quarter]="Q1")*(Sales[Amount]))Cause 2: Text Stored as Numbers in Your Criteria Range
If the values in your criteria column look like numbers but were imported from a CSV, copied from a web page, or exported from a database, they may be stored as text. Excel left-aligns them and sometimes shows a small green triangle in the corner of the cell.
When your formula checks (B2:B100=2023), it compares a numeric 2023 against text strings like
Frequently Asked Questions
Why does SUMPRODUCT return 0 when I can see matching rows in my data?
SUMPRODUCT returns 0 when at least one of its condition arrays evaluates entirely to FALSE or zero. The most common causes are mismatched range sizes, text-stored numbers that don't match numeric criteria, or invisible leading and trailing spaces in cell values.
How do I find out which condition in my SUMPRODUCT formula is failing?
Isolate each condition by wrapping it alone in a SUMPRODUCT and checking if it returns a count greater than zero. For example, =SUMPRODUCT((A2:A100="East")*1) should return the number of matching rows; if it returns 0, that condition is the problem.
Does SUMPRODUCT handle text criteria the same way SUMIFS does?
Not exactly. SUMPRODUCT is case-insensitive by default like SUMIFS, but it does not support wildcard characters such as * and ? unless you wrap the condition in ISNUMBER(SEARCH()). Using wildcards directly in a SUMPRODUCT comparison will cause the condition to return FALSE for every row.
What is the double-negative trick in SUMPRODUCT and when do I need it?
The double-negative (--) converts a Boolean array of TRUE/FALSE values to 1/0 integers before multiplication. You need it when a condition array is not multiplied by another array or a numeric range, for example =SUMPRODUCT(--(A2:A100="East")) to count matches rather than sum values.
Can mismatched data types between my criteria and the cell values cause SUMPRODUCT to return zero?
Yes. If your cells contain the number 2023 but your criterion is the text "2023", Excel treats them as unequal and the condition evaluates to FALSE for every row. Use VALUE() to convert text-stored numbers or ensure your criteria literal matches the stored data type.
📤 Share this article
Sign in to saveRelated Articles
Comments (0)
No comments yet. Be the first!