Microsoft Office Tutorials and References

In Depth Information

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

Now you can use the named range
Holidays
as the third argument of the

WORKDAY
and
NETWORKDAYS
functions.

Gotcha:
While these names work ﬁ ne with
WORKDAY
and
NETWORKDAYS,

they fail in complex array formulas.

Summary:
You can convert a range of dates to a named array to simplify the

use of the
WORKDAY
and
NETWORKDAYS
functions.

SUM EVERY OTHER ROW

OR EVERY THIRD ROW

Challenge:
In Figure 7, someone set up a worksheet with dollars in rows 2, 4,

6, 8, and so on and percentages in rows 3, 5, 7, 9, and so on. You want to sum

only the dollars, which are stored in the even rows. While you’re at it, you’d like

to know how to sum the odd rows or every third row.

Solution:
There are a lot of possible approaches to this problem, some of

which require you to ﬁ gure out which rows to sum.

To sum the odd rows:
=SUMPRODUCT(MOD(ROW(3:100),2),(C3:

C100))

To sum the even rows:
=SUMPRODUCT(--

(MOD(ROW(2:99),2)=0),(C2:C99)) or =SUMPRODUCT(MOD(ROW

(1:98),2),(C2:C99))

To sum every third row (2, 5, 8, etc.):
=SUMPRODUCT(--(MOD(ROW(2:

148),3)=2),(C2:C148)).
See Figure 8

•

•

•

Figure 7.
You want to sum the even rows.

Summing the Odd Rows

Think back to when you were just learning division. If you had the problem 38

divided by 5, you would write that the answer is 7 with a remainder of 3. Excel