MS-Excel / Functions and Formula

Using an Array Formula

You could use an array formula to SUM every second cell in the range $A$1:$A$10; for example:

=SUM(IF(MOD(ROW($A$1:$A$10),2)=0,$A$1:$A$10,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$10),2)=0,$A$1:$A$10,0))}

You must let Excel add the curly 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.

[Contents] [Next]

In this tutorial:

  1. Sum Every Second, Third, or Nth Row or Cell
  2. Using an Array Formula
  3. Using SUMPRODUCT
  4. Using DSUM