Summing Every Nth Cell (rows)

[Richard Crane]

Hi, I'm trying to sum a large spreadsheet, with the value I'd like to
sum in the row in cells B, D, F, H...........etc. i.e. every second
cell. Is there an easy way to do this?

Thanks

 

[Chip Pearson]

Richard,

Use a formula like the following:

=SUMPRODUCT(A1:A10*(MOD(A1:A10,2)=1))

This will sum the odd numbered rows in A1:A10. Change the ending
"=1" to "=0" to sum the even numbered rows.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Richard Crane >" <<[email protected]>
wrote in message
news:[email protected]...

 

[Richard Crane]

=SUMPRODUCT($B$8:$FA$8*(MOD($B$8:$FA$8,2)=0))

Above is the formula I've entered, but it's returning "0". Am I doing
something wrong? p.s. the cells that are being summed are all Vlookups,
does this make a difference, as when I copy, paste special values I get
value of 140, still well short od the 79 million I should have!

 

[Dave Hawley]

Hi Richard

You might want o read this page on how to do this.

http://www.ozgrid.com/Excel/sum-every-2nd-nth-cell.htm

It's a more flexible, faster and more bullet proof method.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Chip Pearson]

Sorry, the formula should be

=SUMPRODUCT(A1:A10*(MOD(ROW(A1:A10),2)=1))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Richard,

Use a formula like the following:

=SUMPRODUCT(A1:A10*(MOD(A1:A10,2)=1))

This will sum the odd numbered rows in A1:A10. Change the ending
"=1" to "=0" to sum the even numbered rows.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Richard Crane >" <<[email protected]>
wrote in message

 

[Richard Crane]

As I'm working horizontally i.e. summing every second column, can
assume I'm changing row->column in these fomulas.

p.s. Thanks for the help folks

 

[RagDyeR]

This should sum every second column from B:Z in row 4:

=SUMPRODUCT((MOD(COLUMN($B$4:$Z$4),2)=0)*($B$4:$Z$4))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


message As I'm working horizontally i.e. summing every second column, can I
assume I'm changing row->column in these fomulas.

p.s. Thanks for the help folks.

 

[Richard Crane]

Thanks folks, all working fine now!