Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 69 Sum Every Second, Third, or nth Row or Cell

Every now and then you might want to sum every
second, third, fourth, etc., cell in a spreadsheet. Now you can, with
the following hack.

Excel has no standard function that
will sum every nth cell or row. However, you can
accomplish this in a number of different ways. All these approaches
use the ROW function and the
MOD function.

The ROW function returns the row number of a
single cell reference:

ROW(reference)

The MOD function
returns the remainder after number is
divided by divisor:

MOD(number,divisor)

Nest the
ROW function within the MOD
function (to supply the number argument), divide it by 2 (to sum
every second cell), and check to see whether the result is 0 (zero).
If it is, the cell is summed.

You can use these functions in numerous
wayssome of them producing better results than others. For
instance, an array formula to SUM every second
cell in the range $A$1:$A$100 could look like this:

=SUM(IF(MOD(ROW($A$1:$A$500),2)=0,$A$1:$A$500,0))

Because this is an array formula, you must enter it by pressing Ctrl-Shift-Enter. Excel will add the curly brackets so that it looks like this: {=SUM(IF(MOD(ROW($A$1:$A$500),2)=0,$A$1:$A$500,0))} You must let Excel add these brackets, as adding them yourself will cause the array formula to fail.

Although this will do the job, it
is not good spreadsheet design to use this method. It is an
unnecessary use of an array formula. To make matters worse, it has
the volatile ROW function nested within it, making
the whole array formula volatile. This means the formula would
constantly recalculate whenever you are working in the workbook. This
is a bad way to go!

Here's another formula you can use, which is a
slightly better choice:

=SUMPRODUCT((MOD(ROW($A$1:$A$500),2)=0)*($A$1:$A$500))

You should, however, be aware that this
formula will return #VALUE! if any cells in the
range contain text rather than numbers. This formula, although not a
true array, also will slow down Excel if too many instances of it are
used, or if those instances reference a large range.

Fortunately, there is a much better
way that is not only more efficient, but also far more flexible. This
requires using the DSUM function. For this
example, we used the range A1:A500 as the range for which we need to
sum every nth cell.

Enter
the word Criteria in cell E1. In cell E2,
enter this formula:

=MOD(ROW(A2)-$C$2-1,$C$2)=0

Select cell C2 and then
select Data Validation. Select List from the Allow: box,
and in the Source: box, type:
1,2,3,4,5,6,7,8,9,10. Ensure that the In-Cell
drop-down box is checked and click OK. In cell C1, enter
SUM every..... In any
cell after row 1, enter this formula:

=DSUM($A:$A,1,$E$1:$E$2)

In the cell
directly above where you entered the DSUM
function, enter this:

 ="Summing Every " & $C$2 & 
CHOOSE($C$2,"st","nd","rd","th","th","th","th","th","th","th") & " Cell"

Now all you need to do is choose the desired number from cell C2 and
the DSUM function will do the rest.

As you
can see from Figure 6-11, you can use one
DSUM function to sum each cell at the interval you
specify. The DSUM function is a far more efficient
formula than an array formula or the SUMPRODUCT
function. Although setup can take a little more time,
it's really a case of a little pain for a lot of
gain.

Figure 6-11. Possible end result with formatting