Microsoft Office Tutorials and References
In Depth Information
Excel Functions for Your Data Model
Figure 11-12: Without the SUMPRODUCT, getting the total sales for each year involves a two-step process: First
multiply price and units and then sum the results.
Figure 11-13: The SUMPRODUCT function allows you to perform the same analysis with just three formulas
instead of 11.
The syntax of the SUMPRODUCT function is fairly simple:
SUMPRODUCT( array1,array2, ...)
The array argument represents a range of data. You can use anywhere from two to 255 arrays in a
SUMPRODUCT formula. The arrays are multiplied together and then added. The only hard-and-fast
rule you have to remember is that all the arrays must have the same number of values. That is to say,
you can’t use the SUMPRODUCT if range X has 10 values and Range Y has 11 values. Otherwise, you
get the #VALUE! error.
A twist on the SUMPRODUCT function
The interesting thing about the SUMPRODUCT function is that you can use it to filter out values. Take
a look at Figure 11-14 to see what I mean.
The formula in cell E12 is pulling the sum of total units for just the North region. Meanwhile, cell E13
is pulling the units logged for the North region in the year 2011.