1. ## 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.

3. ## 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.

#### Posting Permissions

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