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:
To sum the even rows: =SUMPRODUCT(--
(MOD(ROW(2:99),2)=0),(C2:C99)) or =SUMPRODUCT(MOD(ROW
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