sum values on even row numbers

Chris Davison · Feb 21st, 2002, 04:31 AM

Morning all,

I thought this would be a 2-minute formula but it's eluded me this morning

Range A1 to A20 contains values

I just want a single formula that will sum those sitting on even row numbers

many thanks
Chris

Derek · Feb 21st, 2002, 05:04 AM

Not flash, but works.
=A2+A4+A6+A8+A10+A12+A14+A16+A18+A20
Derek

Chris Davison · Feb 21st, 2002, 05:15 AM

G'Day Derek,

yeah, my ranges are actually a lot bigger, so ideally I'm trying to use something based on that row number or an even/odd basis

sorry, I should have been clearer mate !

Chris

Juan Pablo González · Feb 21st, 2002, 05:23 AM

See Tip Of the Day for Thursday, January 24, 2002

http://www.mrexcel.com/weblog/weblog.shtml

Feb 21st, 2002, 05:25 AM

Chris,

I believe that this should do the trick for you:

=SUM(IF(MOD(ROW(A1:A20),2)=0,A1:A20))

Enter it as an array formula by pressing Ctrl+Shift+Enter all at once.

enjoy

Bariloche

Derek · Feb 21st, 2002, 05:26 AM

G'Day Chris
Sorry mate, I just couldn't resist it.
This is probably something you can do with an array formula but I can only do it the hard way by putting this in B1, scrolling down, then summing it
=IF(ISEVEN(ROW()),A1,0)
Perhaps this will help until someone cleverer than me comes along
Have a good day
Derek

Chris Davison · Feb 21st, 2002, 05:49 AM

Fab, thanks guys

Juan Pablo - thanks, I should remember about those tips : one quick question though, I couldn't understand what the "-row(A1)" in the mod function was there for

=SUM((MOD(ROW(A1:A250)-ROW(A1),2)=0)*(A1:A250))

Juan Pablo González · Feb 21st, 2002, 07:15 AM

For that, i give FULL CREDIT to Aladin...

The -ROW(A1) is to "adapt" the formula to whatever range, doesn't matter in which row it starts. In other words, "offset" the range to row 0 (Or was it 1 ?)

Feb 21st, 2002, 05:23 AM	#4
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	See Tip Of the Day for Thursday, January 24, 2002 http://www.mrexcel.com/weblog/weblog.shtml __________________ Regards, Juan Pablo González http://www.juanpg.com

Feb 21st, 2002, 07:15 AM	#8
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	For that, i give FULL CREDIT to Aladin... The -ROW(A1) is to "adapt" the formula to whatever range, doesn't matter in which row it starts. In other words, "offset" the range to row 0 (Or was it 1 ?) __________________ Regards, Juan Pablo González http://www.juanpg.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 21st, 2002, 04:31 AM	#1
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Morning all, I thought this would be a 2-minute formula but it's eluded me this morning Range A1 to A20 contains values I just want a single formula that will sum those sitting on even row numbers many thanks Chris

Feb 21st, 2002, 05:04 AM	#2
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Not flash, but works. =A2+A4+A6+A8+A10+A12+A14+A16+A18+A20 Derek

Feb 21st, 2002, 05:15 AM	#3
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	G'Day Derek, yeah, my ranges are actually a lot bigger, so ideally I'm trying to use something based on that row number or an even/odd basis sorry, I should have been clearer mate ! Chris

Feb 21st, 2002, 05:25 AM	#5
Guest Posts: n/a	Chris, I believe that this should do the trick for you: =SUM(IF(MOD(ROW(A1:A20),2)=0,A1:A20)) Enter it as an array formula by pressing Ctrl+Shift+Enter all at once. enjoy Bariloche

Feb 21st, 2002, 05:26 AM	#6
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	G'Day Chris Sorry mate, I just couldn't resist it. This is probably something you can do with an array formula but I can only do it the hard way by putting this in B1, scrolling down, then summing it =IF(ISEVEN(ROW()),A1,0) Perhaps this will help until someone cleverer than me comes along Have a good day Derek

Feb 21st, 2002, 05:49 AM	#7
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Fab, thanks guys Juan Pablo - thanks, I should remember about those tips : one quick question though, I couldn't understand what the "-row(A1)" in the mod function was there for =SUM((MOD(ROW(A1:A250)-ROW(A1),2)=0)*(A1:A250))