SUMPRODUCT for multi-condition sums
7 min read
Add up values that meet several conditions at once, including conditions SUMIFS cannot handle, without helper columns.
The problem
You have a sales list and need one number: the total for a given region, a given product and a given year. Most people reach for SUMIFS, and for simple cases that is the right call. But sooner or later a request arrives that SUMIFS cannot express: either-or logic across columns, a test on the month of a date, or a comparison between two columns.
SUMPRODUCT handles all of these in one formula, in every version of Excel, with no helper columns and no special entry keys. The sample data below is used throughout this guide.
| Region | Product | Date | Amount |
|---|
Imagine this in cells A2:D9, with headings in row 1.
How SUMPRODUCT works
SUMPRODUCT multiplies arrays together item by item, then adds up the results. A test like A2:A9="West" produces a list of TRUE and FALSE values. Multiply by another list and Excel turns TRUE into 1 and FALSE into 0. A row only contributes if every test is 1, because anything times 0 is 0.
=SUMPRODUCT((A2:A9="West")*(B2:B9="Widget")*D2:D9)Try it
Choose conditions and watch the formula and the rows that count.
| Region | Product | Date | Amount | Counts? | Counted |
|---|
Three patterns to know
1. AND: multiply the tests
Each test you multiply in is another condition that must be true. Adding a year test only takes one more factor:
=SUMPRODUCT((A2:A9="West")*(B2:B9="Widget")*(YEAR(C2:C9)=2026)*D2:D9)Result with the sample data: $2,150
2. OR: add the tests, then check for greater than zero
To total Widget sales for West or East, add the two region tests. Wrapping the sum in >0 turns the result back into a clean 1 or 0, which matters when the two tests come from different columns and could both be true.
=SUMPRODUCT(((A2:A9="West")+(A2:A9="East")>0)*(B2:B9="Widget")*D2:D9)Result with the sample data: $5,450
3. Tests on a calculation
SUMIFS can filter a date range, but it cannot test the result of a calculation on each row. SUMPRODUCT can. This totals Widget sales made in March of any year:
=SUMPRODUCT((B2:B9="Widget")*(MONTH(C2:C9)=3)*D2:D9)Result with the sample data: $2,450
The same idea lets you compare columns, for example actual greater than budget, or test the first letters of a code with LEFT.
SUMIFS or SUMPRODUCT?
| Use SUMIFS when | Use SUMPRODUCT when |
|---|---|
| All conditions are plain matches that must all be true (AND) | You need OR logic across different columns |
| You are working with large data and want speed | You need to test a calculation, such as the month of a date or the length of a code |
| You want the simplest formula a colleague can read | You need to compare two columns, or compute a weighted sum |
For the plain case, SUMIFS gives the same answer as the first formula, and it is usually faster on big ranges:
=SUMIFS(D2:D9,A2:A9,"West",B2:B9,"Widget")Result with the sample data: $2,850
A bonus: weighted averages
SUMPRODUCT is also the standard way to compute a weighted average. With quantities in B and prices in C:
=SUMPRODUCT(B2:B9,C2:C9)/SUM(B2:B9)Common mistakes
- Ranges of different sizes. Every array must have the same number of rows and columns, or you get a #VALUE! error.
- Text in the numbers column. With the multiplication style, a text entry in the amounts range causes #VALUE!. If you separate the arrays with commas instead, Excel treats text as zero.
- Whole-column references. Using D:D forces Excel to process more than a million rows. Use exact ranges or an Excel Table.
- Hidden spaces. "West " with a trailing space does not equal "West". Clean the data first, or wrap the range in TRIM.
- Dates stored as text. YEAR and MONTH only work on real dates. If the column is left-aligned, check it.
Takeaways
- A test inside parentheses becomes 1 or 0, and multiplying tests means AND.
- Add tests and compare to zero for OR.
- SUMPRODUCT can test calculations that SUMIFS cannot.
- Use exact ranges, keep all ranges the same size, and clean your text.
Reports that update themselves?
If you are building these formulas by hand every month, there is usually a better design. Start with a free 30 minute strategy session.