Microsoft Office Forums Summing Non Contiguous Cells in a Row or Column
 Register FAQ Search Today's Posts Mark Forums Read

#1
12-17-2012, 01:51 PM
 Joe Ottenhof Windows 7 32bit Office 2007 Novice Join Date: Dec 2012 Posts: 3
Summing Non Contiguous Cells in a Row or Column

I need to total a large number of cells in a column that are not contiguous, ie, not next to each other. For example I want the total of every 4th cell in a column without having to manually entering a formula containing the exact cell references...that would be way to tedious and time consuming.
#2
12-18-2012, 01:46 AM
 Kevin@Radstock Windows 7 32bit Office 2010 32bit Office 365 Join Date: Feb 2012 Posts: 94

Hi Joe Ottenhof

Two methods assuming data is in A1:A1000:

1/ =SUMPRODUCT(--(MOD(ROW(A1:A1000),4)=0),A1:A1000)

2/ =SUM((MOD(ROW(A1:A1000),4)=0)*(A1:A1000))
This is an array formula: CTRL + SHIFT + ENTER

You can change what rows to sum, by changing the MOD part ie: =0 to = 1 or 2 etc

Kevin
#3
12-18-2012, 07:04 AM
 Joe Ottenhof Windows 7 32bit Office 2007 Novice Join Date: Dec 2012 Posts: 3
Thanks

Quote:
 Originally Posted by Kevin@Radstock Hi Joe Ottenhof Two methods assuming data is in A1:A1000: 1/ =SUMPRODUCT(--(MOD(ROW(A1:A1000),4)=0),A1:A1000) 2/ =SUM((MOD(ROW(A1:A1000),4)=0)*(A1:A1000)) This is an array formula: CTRL + SHIFT + ENTER You can change what rows to sum, by changing the MOD part ie: =0 to = 1 or 2 etc Kevin
Kevin
Thanks very much. The second way works perfectly. The first one gives me an error. I paid a guy \$38 and he didn't get the answer...so once again thanks,
#4
12-18-2012, 07:08 AM
 Joe Ottenhof Windows 7 32bit Office 2007 Novice Join Date: Dec 2012 Posts: 3

Sorry Kevin...the first one does work as well. They both work thanks once again.

Joe O
#5
12-19-2012, 04:13 AM
 Kevin@Radstock Windows 7 32bit Office 2010 32bit Office 365 Join Date: Feb 2012 Posts: 94

Hi Joe

Thanks for the feed back, glad I was of some help.

Kevin

 Tags indexing, sum alternate cells

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Mr Davo Excel 1 10-29-2012 01:07 AM carpat Excel 0 01-10-2012 09:34 AM PRADEEPB270 Excel 1 11-04-2011 03:46 AM danbenedek Excel 0 06-15-2010 12:40 AM LOGISTICS1 Excel 1 04-18-2006 06:00 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:50 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top