SUMPRODUCTarray1

SUMPRODUCTarray1 is the first array argument in Excel’s SUMPRODUCT function. SUMPRODUCT multiplies corresponding elements of the supplied arrays and then sums those products. The array1 input is the primary data set used for the elementwise multiplication and can be a range, an array constant, or the result of an expression. If only array1 is provided, SUMPRODUCT simply returns the sum of its elements.

array1 can contain numbers, booleans (TRUE/FALSE, treated as 1/0 in numeric calculations), or values produced by formulas. For products across several arrays, all inputs must have the same dimensions: for 1-D inputs, equal length; for 2-D inputs, identical row and column counts. A mismatch yields a #VALUE! error. Text values that cannot be interpreted as numbers typically cause a #VALUE! error, whereas numeric strings can be coerced to numbers.

Typical usage: =SUMPRODUCT(A2:A5, B2:B5) returns the sum of products of each pair A2*B2 + A3*B3, and so on. Conditions can be incorporated by coercing logical tests to 1/0, for example: =SUMPRODUCT(--(A2:A5>100), B2:B5). This enables multi-criteria calculations without entering an array formula.

SUMPRODUCT with array1 is widely used for multi-criteria calculations and conditional sums. It works in modern Excel without requiring CSE, but performance can be a consideration on large datasets. It is often used as an alternative to more limited criteria tools, or in scenarios where arrayed logical tests must drive arithmetic in a single function.