Chapter 16: Introducing Array Formulas
359
The following formula uses the SUM function, with the preceding array constant as its argument.
The formula returns the sum of the values in the array (which is 3 ):=SUM({1,0,1,0,1})Notice that this formula uses an array, but the formula itself isn’t an array formula. Therefore, you
don’t use Ctrl+Shift+Enter to enter the formula — although entering it as an array formula will still
produce the same result.Note
When you specify an array directly (as shown previously), you must provide the curly brackets around the
array elements. When you enter an array formula, on the other hand, you do not supply the brackets. n
At this point, you probably don’t see any advantage to using an array constant. The following for-
mula, for example, returns the same result as the previous formula. The advantages, however, will
become apparent.=SUM(1,0,1,0,1)This formula uses two array constants:=SUM({1,2,3,4}*{5,6,7,8})This formula creates a new array (in memory) that consists of the product of the corresponding
elements in the two arrays. The new array is{5,12,21,32}This new array is then used as an argument for the SUM function, which returns the result ( 70 ).
The formula is equivalent to the following formula, which doesn’t use arrays:=SUM(1*5,2*6,3*7,4*8)Alternatively, you can use the SUMPRODUCT function. The formula that follows is not an array for-
mula, but it uses two array constants as its arguments.=SUMPRODUCT({1,2,3,4},{5,6,7,8})A formula can work with both an array constant and an array stored in a range. The following for-
mula, for example, returns the sum of the values in A1:D1, each multiplied by the corresponding
element in the array constant:=SUM((A1:D1*{1,2,3,4}))This formula is equivalent to=SUM(A1*1,B1*2,C1*3,D1*4)