Microsoft Office Tutorials and References

In Depth Information

**SUM EVERY OTHER ROW OR EVERY THIRD ROW**

provides the
MOD
function to return the remainder in a division problem. –For

example,
=MOD(7,2)
calculates 7 divided by 2 and returns 1 as the remainder.

The remainder of an odd number divided by 2 is 1. It is 0 for all even numbers.

You can therefore use
MOD
to assign a
1
to each odd-numbered row and a
0

to each even-numbered row.

The problem is simple if you want only the odd rows. You can use an array of

1s
and
0s
in
SUMPRODUCT
. Multiplying the range
C3:C100
by the result of the

MOD
function (an array of alternating
1s
and
0s
) results in every other number

being added up.

Summing the Even Rows

The
MOD(ROW(),2)
function returns 1 for an odd row, and it returns
0
for

an even-numbered row. Therefore, if the result of the
MOD
function is
0
, you

know you’re working with an even-numbered row. Using
MOD(ROW(),2)=0

will return an array of
TRUE
and
FALSE
values. You can then use the double

minus sign to convert the
TRUE/FALSE
values to
1/0
values.

A simpler but less intuitive solution is to adjust the
MOD
argument so that it is

one row behind the sum range. If you hope to grab the even rows from
C2:

C99
, you can specify a range for the
ROW
function that starts one row above the

real range. Use
MOD(ROW(1:98),2)
to ensure that the ﬁ rst value
MOD
returns

is
1
, followed by
0, 1, 0, 1,
and so on.

Summing Every Third Row

Figure 8 shows a situation in which cost rows have been added. In this case,

you would like to sum every third row—rows 2, 5, 8, etc. If you use
=MOD(Row,

3)
, you get
1
for rows 1, 4, and 7. You get 2 for rows 2, 5, and 8. You get 0 for

rows 3, 6, and 9. To sum only the sales rows, you need to test if the result of

the
MOD
function is a 2. Since this test will return
True/False
values, use the

double minus to convert the
True/False
values to 1/0 values. So the formula

becomes:

=SUMPRODUCT(--(MOD(ROW(2:148),3)=2),(C2:C148))

Part

I

Figure 8.
You want to sum every third row.