Part II: Working with Formulas and Functions
The goal is to calculate the sum of the total product sales without using the individual cal-
culations that were in column D.
FIGURE 18.2
The array formula in cell C9 calculates the total sales without using intermediate formulas.
The following array formula is in cell C9:
{=SUM(B2:B7*C2:C7)}
When you enter this formula, make sure that you press Ctrl+Shift+Enter (and don’t type the
curly brackets because Excel automatically adds them for you).
This formula works with two arrays, both of which are stored in cells. The first array is
stored in B2:B7, and the second array is stored in C2:C7. The formula multiplies the corre-
sponding values in these two arrays and creates a new array (that exists only in memory).
The new array consists of six values, which can be represented like this (the reason for
using semicolons is explained a bit later):
{150;1000;100;90;180;200}
The SUM function then operates on this new array and returns the sum of its values.
In this case, you can use the SUMPRODUCT function to obtain the same result without using an array formula:
=SUMPRODUCT(B2:B7,C2:C7)
As you can see, however, array formulas allow many other types of calculations that are
otherwise not possible.
