• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

6. Hacking Formulas and Functions > 69. Sum Every Second, Third, or nth Row or ...

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.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint