If only one array is supplied, SUMPRODUCT will simply sum the items in the array.
Up to 30 ranges or arrays can be supplied.
When you first encounter SUMPRODUCT, it may seem boring, complex, and even pointless.
But SUMPRODUCT is an amazingly versatile function with many uses.
We need the items inarray1to be numeric, and this is where the double-negative is useful.
Once we have 1s and 0s, we can perform various operations on the arrays withBoolean logic.
The trick is to use Boolean logic, where OR logic is represented with addition (+).
Also, seethis example, which explains several ways to approach a problem like this.
The result is the same, but this syntax provides several advantages.
First, the formula is more compact, especially as the logic becomes more complex.
But the most important advantage isflexibility.
When using separate arguments, the operation is always multiplication, since SUMPRODUCT returns the sum ofproducts.
This limits the formula to AND logic since multiplication corresponds to addition inBoolean algebra.
With the above advantages in mind, there is one disadvantage to the abbreviated syntax.
SUMPRODUCT is programmed to ignore the errors that result from multiplying text values in arrays given asseparate arguments.
This can behandy in certain situations.
With the abbreviated syntax, this advantage goes away, since the multiplication happens inside a single array argument.
In this case, the normal behavior applies: text values will create #VALUE!
SeeWhy SUMPRODUCT?for more details.
Because SUMPRODUCT is built to work with arrays, it is able to perform calculations on the arrays directly.
Then you could use SUM to add up all 10 numbers.
Then SUMPRODUCT will simply sum all values and return the result, with no helper column needed.
See examples below of many other ways to use SUMPRODUCT.
Using SUMPRODUCT means the formulas will work inany versionof Excelwithout special handling.
InExcel 365, the formula enginehandles arrays natively.
For more details and examples, seeWhy SUMPRODUCT?
Criteria can be applied to dates, numbers, and text.
