# Thread: Addition every nth row (Excel 2002 SP3)

1. ## Addition every nth row (Excel 2002 SP3)

Is there a function in Excel which will add values from every nth row in a data set?

2. ## Re: Addition every nth row (Excel 2002 SP3)

Not specifically, but a simple formula should do it (wouldn't surprise me if Hans didn't have one up his sleeve, ready to go).

A littlle more info about what you're trying to achieve, maybe a sample scenario, should get you a more solution.

3. ## Re: Addition every nth row (Excel 2002 SP3)

Within one worksheet, I have set up 50-row blocks to contain data related to a single item (60 columns (years) wide). There are 25-30 items within the worksheet. At the bottom of the worksheet, I want a total, for example, for all of the 3rd rows within each 50-row block.

4. ## Re: Addition every nth row (Excel 2002 SP3)

There's no built-in function for this (as far as I know).

Let's say that you want to add every 5th cell in A1:A100, starting at row 2. You can use the following formula:

=SUMPRODUCT((MOD(ROW(A1:A100),5)=2)*A1:A100)

5. ## Re: Addition every nth row (Excel 2002 SP3)

Thanks very much. That's exactly what I was looking for.

6. ## Re: Addition every nth row (Excel 2002 SP3)

This formula works beautifully ... but I'm not sure I understand why. Would you be able to break it down, so mere mortals like myself can understand step by step. Thanks.

7. ## Re: Addition every nth row (Excel 2002 SP3)

The SUMPRODUCT function takes two (or more) arrays, multiplies the corresponding elements and adds the results. For example, SUMPRODUCT(A1:A3,B1:B3) is evaluated as A1*B1 + A2*B2 + A3*B3.

In the formula I posted, the two arrays are MOD(ROW(A1:A100),5)=2 and A1:A100. The latter is the column containing the values that you want to add.
ROW(A1:A100) returns the list of row numbers in A1:A100, i.e. 1, 2, 3, 4, 5, 6, 7, 8, ...., 99, 100.
MOD(...,5) returns the remainder of the first argument after division by 5.
So MOD(ROW(A1:A100),5) returns 1, 2, 3, 4, 0, 1, 2, 3, ..., 4, 0.
MOD(ROW(A1:A100),5)=2 returns a list of TRUE/FALSE values - TRUE if the remainder is 2, FALSE otherwise: FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, ...
In calculations in Excel, FALSE is equivalent to 0 and TRUE is equivalent to 1, so we have 0, 1, 0, 0, 0, 0, 1, 0, ...
As you see, every 5th element starting with the 2nd equals 1, all others are 0.
When we combine this with A1:A100 in SUMPRODUCT, only the cells in row 2, 7, 12 etc. contribute to the result because they are multiplied with 1, the others don't because they are multiplied with 0.

Clear as mud now? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

8. ## Re: Addition every nth row (Excel 2002 SP3)

Thank you so much for taking the time to explain this. Amazing!

9. ## Re: Addition every nth row (Excel 2002 SP3)

Same sleeve as the formula?

If Hans' explanation needs further explanation you can also use the built-in Excel resources.

The "fx" (insert function) button on the formula bar (or Shift-F3) describes and allows you to select among available functions, and guides you through parameter entry, step by step. If the brief guidance in the dialog is insufficient, you can click on the "Help on this function" link (bottom left) to take you direct to function specific help.

What I often do to build up a complex composite of functions, as per Hans' solution, is to work out each component in a separate cell before combining them. This process is especially useful to get my head around unfamiliar functions.

(If you only have single copy of your (precious) formula, make a copy before trying the following.)
The other trick I use is the F9 key for partial calculations (to debug formulae). If you select a part of the formula in the formula bar, and press F9, the selection is replaced by its calculated value. The selection can be anything from a single cell reference, to one, or more, functions. When you make the selection, limit cell references to to just the cell reference, and for functions include everything from the function name to the closing parenthesis. Repeated select/F9s let you "step" through a formula to see how the final result is calculated. You can use ESC to "back out" from the partial calculation(s) - ENTER will save the formula with the calculated values in place.

If you're really keen, you can always dive straight into the Excel help, but be prepared to guess the sometimes elusive, correct terminology (as with most help systems).

#### Posting Permissions

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