Sum every 10th cell in a range of cells?

J

JKJ95

Is it possible to Sum every 10th cell in a range of cells (i.e.
a10+a20+a30....in a range of a1:a500) without entering every cell manually?
Thanks.
 
B

Bernie Deitrick

JKJ95

=SUMPRODUCT((MOD(ROW(A1:A500),10)=0)*A1:A500)

HTH,
Bernie
MS Excel MVP
 
D

Domenic

Try...

=SUMPRODUCT(--(MOD(ROW(A1:A500)-CELL("row",A1)+1,10)=0),A1:A500)

In instances where you want to sum every 10th cell starting from A1,
change the +1 part of the formula to +0...

=SUMPRODUCT(--(MOD(ROW(A1:A500)-CELL("row",A1)+0,10)=0),A1:A500)

Hope this helps!
 
T

Tom Ogilvy

=SUM(IF(MOD(ROW(A1:A500),10)=0,A1:A500))

Entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.
 
R

Ron Coderre

Try something like this:
=SUMPRODUCT(--(MOD(ROW($A$10:$A$30)-ROW($B$10),10)=0)*$A$10:$A$30)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top