Every 4th

[lightbulb]

Is there a way to add up every fourth row? For example I want to start at row
6 and add every 4th row after that (10,14,18,22...etc).

Any help?

Thanks!

 

[T. Valko]

Try this:

=SUMPRODUCT(--(MOD(ROW(A6:A100)-ROW(A6),4)=0),A6:A100)

That will sum A6, A10, A14, A18, A22 etc., etc.

 

[Dave Peterson]

Rick and Biff have given you solutions -- but I wouldn't use them.

If you insert/delete even a single row, your formula may not give the results
you want.

Instead, I'd do a little more work.

I'd add a helper column that would be used as an indicator that the
corresponding cell in that row should should be included in the sum. Then I'd
put something like an "X" in that indicator cell (yep, all the cells).

For instance, if column A was that indicator and I wanted to add the values in
column C:

=sumif(a:a,"x",c:c)

Using this may seem like more work, but if you share this workbook with
others--or your memory is like mine, then it may be a safer technique.

 

[T. Valko]

If you insert/delete even a single row,

your formula may not give the results you want.

If you insert a new row above the referenced range the formula I suggested
will account for that. If you delete a row above the referenced range the
formula I suggested will account for that. If you delete or insert a new row
within the referenced range the formula will still calculate the range based
on the interval of n which would be correct.

If you want to lock the formula to *specific rows* regardless of row
insertions/deletions then you'd need to use something like this:

=SUMPRODUCT(N(INDIRECT({"A6","A10","A14","A18","A22"})))

 

[Dave Peterson]

It still scares me.

I had a workbook that determined the extended cost of a bunch of items in
columns. Each item started off by having a column dedicated to a description,
another column for quantity and another for unit cost.

The formula worked fine until the person who was the real owner of the workbook
inserted another description column for some entries (breaking the rules of the
layout) and broke my formula.

I learned a lesson then.