Excel 2010 Bible

Chapter 17: Performing Magic with Array Formulas

389

FIGURE 17.6

An array formula returns the sum of every nth value in the range.

The following array formula returns the sum of every nth value in the range:

{=SUM(IF(MOD(ROW(INDIRECT(“1:”&COUNT(Data)))-1,n)=0,Data,””))}

This formula returns 70 , which is the sum of every third value in the range.

This formula generates an array of consecutive integers, and the MOD function uses this array as its

first argument. The second argument for the MOD function is the value of n. The MOD function cre-

ates another array that consists of the remainders when each row number is divided by n. When

the array item is 0 (that is, the row is evenly divisible by n), the corresponding item in the Data

range will be included in the sum.

You find that this formula fails when n is 0 : that is, when it sums no items. The modified array for-

mula that follows uses an IF function to handle this case:

{=IF(n=0,0,SUM(IF(MOD(ROW(INDIRECT(“1:”&COUNT(data)))-

1,n)=0,data,””)))}

This formula works only when the Data range consists of a single column of values. It does not

work for a multicolumn range or for a single row of values.