Microsoft Office Tutorials and References

In Depth Information

Figure 15-9:
An array formula returns the sum of every
n
th value in the range.

The following array formula returns the sum of every
n
th 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 argu-

ment. The second argument for the MOD function is the value of
n
. The MOD function creates another array

that consists of the remainders when each row number minus 1 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 formula that

follows uses an IF function to handle this case: