SUMPRODUCT vs SUMIFS
Both add with conditions. SUMIFS is faster and easier to read; SUMPRODUCT accepts any calculation as a condition.
| SUMPRODUCT | SUMIFS | |
|---|---|---|
| "OR" conditions | Yes, by adding conditions | No (you add two formulas) |
| Condition on a calculation (MONTH(A2:A9)=3) | Yes | No |
| Multiply columns before adding | Yes, it is its original job | No |
| Speed on thousands of rows | Slower | Faster |
Syntax and example
SUMPRODUCT
=SUMPRODUCT(array1, [array2], …)
=SUMPRODUCT(B2:B6,C2:C6)
Multiplies ranges row by row and adds up the results: the total of quantity × price in one formula.
SUMIFS
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
=SUMIFS(D2:D11,B2:B11,G2,C2:C11,G3)
Adds only the rows that meet every condition.
Which one to use?
Start with SUMIFS. Switch to SUMPRODUCT when the condition needs a calculation or an "OR".
About Ads
This site uses Google AdSense to display ads. These ads help us keep WebHerramientas completely free. Ads are managed by Google and meet strict security and privacy standards.
Ads help us keep WebHerramientas free for everyone
Practice it
Solve the exercise on the sheet; it is checked instantly. See all lessons · Formula translator
SUMPRODUCT
SUMPRODUCT multiplies the ranges row by row and adds up the results. It gives the total of a shopping list without a helper column.
In F2, calculate the shopping total (quantity times price for each item, all added up) in a single formula.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Quantity | Price | ||||
| 2 | Milk | 6 | 28 | Shopping total | |||
| 3 | Eggs (dozen) | 2 | 52 | ||||
| 4 | Bread | 3 | 45 | ||||
| 5 | Apples (kg) | 1.5 | 48 | ||||
| 6 | Coffee | 1 | 189 | ||||
| 7 |
Tip: while typing, click a cell or drag across several to insert their reference. F4 anchors the reference with $.
Frequently asked questions
- What is the difference between SUMPRODUCT and SUMIFS?
- Both add with conditions. SUMIFS is faster and easier to read; SUMPRODUCT accepts any calculation as a condition.
- When should I use SUMPRODUCT or SUMIFS?
- Start with SUMIFS. Switch to SUMPRODUCT when the condition needs a calculation or an "OR".