Math and trigonometry
SUMPRODUCT Function
Returns the sum of the products of
corresponding array components.
by
Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

Multiplies corresonding components in the given arrays and
returns the sum of those products. Although SUMPRODUCT treats
its arguments as arrays, it doesn't need to be arrayentered.
Syntax, Method 1
With Method 1, the standard approach, each array is placed in a separate argument:
SUMPRODUCT(array1, [array2],...)
 Array1 Required. The first array you
want to multiply and then add.
 Array2,...
Array arguments 2 to 255 whose components you want to
multiply and then add.
Syntax, Method 2
With Method 2, at least two arrays—and typically, all
arrays—are multipled within one
argument:
SUMPRODUCT(array1*[array2]*...)
 Array1 Required. The first array you
want to multiply and then add.
 Array2,...
Any number of arrays
whose components you want to multiply and then add.
Managers... 
Charley Kyd can
personally help you to use SUMPRODUCT and other
functions in your own organization.
Click here to learn more. 
Applies To
Excel 2003 and above
Remarks
 SUMPRODUCT can return results from a closed external
workbook.
 The array arguments must have the same dimensions. If
they do not, SUMPRODUCT returns the #VALUE! error value.
 SUMPRODUCT treats array entries that are not numeric as
if they were zeros.
 As shown in Examples 4 and 5 below, if the array arguments
are logical tests, Method 1 requires that the TRUE and FALSE values be
converted to 1 and 0 respectively.
 SUMPRODUCT generally is more powerful that
SUMIFS, but calculates
more slowly.
 SUMPRODUCT can't support
wildcard characters
very easily.
Examples
You can
download this example workbook here,
along with all other example workbooks I've completed for this
Excel help area.
The following examples all reference this simple product
database.
When you multiply numeric values, both Methods produce the same result.
This example fails because the first argument returns logical values (TRUE or FALSE), not numeric values.
These examples succeed because adding 0 to a logical argument, or multiplying it
by 1, converts TRUE to 1 and FALSE to 0. Doing so converts the results to a numeric
argument that SUMPRODUCT can multiply.
This example succeeds
with Method 2 because multiplying the arrays automatically converts the TRUE and FALSE values to 1 and 0.
Using Method 2 allows you to mix logical and numeric arrays easily.
You can mix Method 1 and Method 2 as long as each argument returns numeric values.
Unlike the SUMIFS and COUNTIFS functions, which perform somewhat
similar tasks, SUMPRODUCT can rely on additional worksheet functions. Here, for example…
 Example 9 counts the number of Products with five characters in its name.
 Example 10 finds the total Units for all Regions with "n"
in their names.
 Example 11 finds the total Units for all Product names
beginning with "H".
Other ExcelUser
Information About SUMPRODUCT
Other Help
