Sum every 10th cell in a range of cells?
 

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.

JKJ95

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

HTH,
Bernie
MS Excel MVP


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

=SUMPRODUCT((A1:A500)*(MOD(ROW(1:500),10)=0))
Bob Umlas
Excel MVP

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

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!

In article ,
"JKJ95" wrote:

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.

=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.

--
Regards,
Tom Ogilvy


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

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

"JKJ95" wrote:

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.

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

--
Regards,
Ron

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

Jerry

JKJ95 wrote:

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.

You probably meant

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

Alok Joshi

"Jerry W. Lewis" wrote:

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

Jerry

JKJ95 wrote:

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.

True

Alok wrote:

You probably meant

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

Alok Joshi

"Jerry W. Lewis" wrote:


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

Jerry

JKJ95 wrote:


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.