SUMPRODUCT multiplies the matching numbers in two or more equal sized ranges and adds up the results in one step, so =SUMPRODUCT(B2:B9, C2:C9) gives you the total of quantity times price with no helper column. Its real value is conditional math: multiply a range by a TRUE or FALSE test and it will sum, count, or average only the rows that pass, which is exactly the multiple criteria job that SUMIF and COUNTIF handle awkwardly.
This comes up constantly on converted data. When a PDF invoice or price list lands in a spreadsheet, you usually get a quantity column and a unit price column sitting side by side. SUMPRODUCT turns those into an order total in a single cell, and once you add a condition it totals just the rows for one vendor, one month, or one category. The one thing to watch is that SUMPRODUCT treats text as zero, so an amount that came across as text quietly drops out of the total. That trap gets its own section below.
What does SUMPRODUCT do in Excel?
It takes the arrays you give it, multiplies them position by position, and returns the sum of those products. Give it two columns of eight numbers and it multiplies row 1 by row 1, row 2 by row 2, and so on, then adds the eight answers together. The arrays have to be the same shape, meaning the same number of rows and columns, or you get an error.
| Formula | What it returns |
|---|---|
=SUMPRODUCT(B2:B9, C2:C9) | Sum of quantity times price across all eight rows |
=SUMPRODUCT(B2:B9) | Just the sum of B2:B9, same as SUM |
=SUMPRODUCT(B2:B9, C2:C9, D2:D9) | Sum of three values multiplied together per row |
With a single array it behaves like SUM, which is not why anyone reaches for it. The point is the weighted total: a weighted average of scores, an order value from quantity and price, a currency conversion of an amount times a rate. All of it happens in one cell with no extra column to hide and maintain.
How do I use SUMPRODUCT with multiple criteria?
Wrap each condition in parentheses and multiply the conditions together, then multiply by the column you want to total. Say column A holds a category, column B a month, and column D the amount:
=SUMPRODUCT((A2:A100="Fuel")*(B2:B100="March")*D2:D100)
Each test produces a column of TRUE and FALSE. Multiplying two of them together gives 1 only where both are true, because TRUE times TRUE behaves as 1 times 1 and anything involving a FALSE becomes 0. Multiply that by the amount column and every row that fails a test contributes zero to the sum. That is how you total one category in one month without a pivot table or a helper column.
To handle an OR instead of an AND, add the conditions rather than multiplying them: (A2:A100="Fuel")+(A2:A100="Tolls") is true for either. Keep an eye on rows that match both, because addition can push a matching row to 2 and double count it.
Why does SUMPRODUCT use a double negative?
The double minus, written --, turns TRUE and FALSE into 1 and 0 when you are not already multiplying them by another array. The first minus flips TRUE to -1 and leaves FALSE at 0, and the second minus flips -1 back to 1. You need it in the counting form, where there is no numeric column to force the conversion:
=SUMPRODUCT(--(A2:A100="Fuel"))
Without the double minus, SUMPRODUCT sees a column of TRUE and FALSE, treats them as text-like values worth zero, and returns 0. In the multiple criteria example above you did not need it, because multiplying the two condition arrays together already did the coercion. The rule of thumb: if your conditions are joined by *, the math is already numeric, and if a condition stands alone, put -- in front of it.
Can SUMPRODUCT count instead of sum?
Yes, and this is where it beats COUNTIFS on messy criteria. To count rows that meet several conditions, multiply the condition arrays and leave out any value column:
=SUMPRODUCT((A2:A100="Fuel")*(C2:C100>100))
That counts the rows where the category is Fuel and the amount is over 100. Because you are building the logic yourself with arrays, you can do comparisons COUNTIFS cannot express in one shot, like counting rows where one column is greater than another: =SUMPRODUCT((D2:D100>E2:E100)*1). The *1 at the end coerces the lone TRUE or FALSE array the same way a double minus would.
SUMPRODUCT vs SUMIFS: which should I use?
Reach for SUMIFS first for plain, single-column criteria, because it is faster to read and faster to calculate. Reach for SUMPRODUCT when the logic goes beyond what SUMIFS can say.
| Situation | Better choice | Why |
|---|---|---|
| Total where category = Fuel and month = March | SUMIFS | Simple equality tests, clearer to read |
| Weighted total: quantity times price | SUMPRODUCT | SUMIFS cannot multiply two columns |
| Count rows where column D is greater than column E | SUMPRODUCT | Compares two ranges row by row |
| Total with an OR across values in one column | SUMPRODUCT | Add the condition arrays together |
| Works in Excel 2010 through 365 | Both | Neither needs a modern version |
SUMPRODUCT is the older, more flexible tool. SUMIFS arrived to make the common case easy. Once you find yourself concatenating helper columns just to make SUMIFS work, that is the signal to switch to SUMPRODUCT and express the whole thing as multiplied arrays. For a straight one-category total, the plain SUMIF and SUMIFS approach stays the cleaner read.
Why is my SUMPRODUCT returning 0 or an error?
Three causes account for almost every failure, and the first is the one that bites converted data hardest.
| Symptom | Cause | Fix |
|---|---|---|
| Total is lower than it should be, or 0 | Numbers are stored as text and count as zero | Convert the column to real numbers first |
| #VALUE! error | The arrays are different sizes | Make every range the same number of rows |
| Condition returns 0 count | A standalone condition has no double minus | Add -- in front, or multiply by 1 |
| Blank result on a whole-column range | A text header sits inside the range | Start the range on the first data row |
The text-as-zero problem is silent, which is what makes it dangerous. SUMPRODUCT does not warn you that half the amounts were ignored, it just returns a total that looks plausible and is wrong. If your figures came out of a PDF, assume they are text until proven otherwise and fix that before you trust any SUMPRODUCT built on them.
Using SUMPRODUCT on data converted from a PDF
The workflow that keeps SUMPRODUCT honest starts before the formula. Convert the file into clean rows with our PDF to Excel converter so the quantity and amount columns land in their own cells, then make sure those columns are genuine numbers. A quick tell is alignment: numbers hug the right edge of a cell, and text numbers sit on the left. If they are on the left, convert the text to numbers before you go any further, because SUMPRODUCT will read every one of them as zero and hand you a total that is quietly short.
Once the numbers are real, SUMPRODUCT earns its keep on the analysis. A weighted total of quantity times price, a category total filtered by month, a count of transactions above a threshold: all of it runs off the converted table with no pivot and no helper columns. If your source documents are receipts rather than a single statement, software that reads receipts and totals every expense category gets you to the same clean, numeric starting point without keying anything by hand. And if a category column has near duplicate spellings like Fuel and fuel, clean those with a controlled dropdown first, or your criteria will silently miss half the rows.
The short version
SUMPRODUCT multiplies equal sized ranges and sums the result. Multiply condition arrays together for AND logic, add them for OR, and put a double minus in front of any condition that stands alone. Use it over SUMIFS whenever you need to multiply two columns or compare one column against another. Before you trust the answer on converted data, confirm the numbers are real numbers and not text, because text counts as zero and nothing warns you.
Last updated July 2026.