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!

Give this formula a try...

=SUMPRODUCT(A6:A1000*(MOD(ROW(A6:A1000)-6,4)=0))

Note: Set the range to whatever you need it to cover. Also, the "6" in the
"-6" towards the end of the formula comes from the starting row for the
range (in case you want to modify this formula for use elsewhere).

--
Rick (MVP - Excel)


"lightbulb" wrote in message
...
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!

Try this:

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

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

--
Biff
Microsoft Excel MVP


"lightbulb" wrote in message
...
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!

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.

lightbulb wrote:

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!

--

Dave Peterson

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","A2 2"})))


--
Biff
Microsoft Excel MVP


"Dave Peterson" wrote in message
...
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.

lightbulb wrote:

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!

--

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.

"T. Valko" wrote:

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","A2 2"})))

--
Biff
Microsoft Excel MVP

"Dave Peterson" wrote in message
...
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.

lightbulb wrote:

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!

--

Dave Peterson

--

Dave Peterson