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 fi 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.
 
Search JabSto ::




Custom Search