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.