Summing Every Nth Cell

[JimS]

I found a solution on the web for summing every Nth cell, but it is
kind of convoluted and I ran into problems.

Let's say I want to sum cells C4,C25,C46...etc all the way down.
Basically every 21st cell.

The problem might be that some of those rows are blank and others
contain text.

Is there an easy way to do this?

Thanks

 

[Dave Peterson]

One way:

=SUMPRODUCT(--(MOD(ROW(C1:C999),21)=4),C1:C999)

Personally, I think I'd use a helper column and put an indicator on every row I
want summed.

Say I put X in column D, then I could use:
=sumif(d:d,"x",c:c)

Then if I ever insert/delete rows, I wouldn't have to worry about the formula
breaking--All I'd have to do is make sure that I use that indicator on any row
that should be accumulated.

 

[Peo Sjoblom]

=SUMPRODUCT(--(MOD(ROW(C4:C5000)-ROW(C4),21)=0),C4:C5000)



--


Regards,


Peo Sjoblom

 

[JimS]

I tried this formula,
=SUMPRODUCT(--(MOD(ROW(C1:C999),21)=4),C1:C999)
but it gave me a circular refererence error when I pasted it into cell
C4.

 

[T. Valko]

Try this:

=SUMPRODUCT(--(MOD(ROW(C4:C500)-ROW(C4),21)=0),C4:C500)

Adjust for the correct end of range

 

[JimS]

I think I erred. In cell C4 is where I want the answer. I want to
sum starting with cells C25,C46,C67, etc.

Sorry about that.

 

[JimS]

Thanks, Dave. I think I've got it now I used this formula and it
seems to work.

Appreciate the help.

=SUMPRODUCT(--(MOD(ROW(C25:C999),21)=4),C25:C999)

 

[ShaneDevenshire]

Hi,

Here is another variation:

=SUMPRODUCT(A1:A100*MOD(ROW(A1:A100),B1)=C1))

This formula is dynamic, by that I mean in B1 you type the Nth number, for
example in your case 21. In C1 you indicate the starting row, so if you want
to count every 21st item starting in cell A3 you type 3 into cell C1.

If this helps, please click the Yes button.

 

[JimS]

I have one more question, and then I'm off to the races. When I pull
this formula down for the other rows in my table, it doesn't work.
I'm guessing that's what the =4 is all about. If I change that to a
=5, then it works for the next cell down.

Is there an easier way other than having to manually change it for
every row in the table?

Thanks

 

[Dave Peterson]

maybe...

=SUMPRODUCT(--(MOD(ROW($C$25:$C$999),21)=row()),$C$25:$C$999)

=row()
returns the row of the cell with the formula. Since you're starting in row 4,
the next formula will be C5 (row 5).

 

[JimS]

Nice, thanks.

 

[JimS]

Works perfectly, Dave. Much appreciated.

 

[Dave Peterson]

You may want to consider adding an extra column and a formula like:

=mod(row(),21)
and drag down

Then use a pivottable to get your summary report.