Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding every nth column (Excel 97)

    Suppose one has a grouping of columns for each month, say a Quantity and Amount and Date for each month of the year. At the end, you want to add January through December quantity and a January through December amount. What kind of formula could add every 3rd column, for instance (apart from the simple hunt and peck each column specifically) ? One could then sum the quantity columns, and copy it to the next column to copy the amounts. In the cited example, it would seem just as easy to hunt and peck, but I wonder about a more generalized formula I can keep in mind for many similar worksheets, and applicable to row setups as well. I keep trying a count of columns or something, but cannot get it to work yet. Perhaps someone has already addressed this problem. Thanks.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Adding every nth column (Excel 97)

    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding every nth column (Excel 97)

    To sum every Nth column in a horizontal range:

    =SUMPRODUCT((MOD(COLUMN($B$2:$F$2)-CELL("Col",$B$2:$F$2)+H1,H2)=0)*($B$2:$F$2))

    To sum every Nth row in a vertical range:

    =SUMPRODUCT((MOD(ROW($A$1:$A$6)-CELL("Row",$A$1:$A$6)+H1,H2)=0)*($A$1:$A$6))

    Set H1 to if you want to start summing from the first cell of the range of interest, otherwise to 1.
    Set H2 to N, the Nth column/row you want to sum.

    You need of course adjust ranges to suit.
    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •