Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-17-2012, 01:51 PM
Joe Ottenhof Joe Ottenhof is offline Summing Non Contiguous Cells in a Row or Column Windows 7 32bit Summing Non Contiguous Cells in a Row or Column Office 2007
Novice
Summing Non Contiguous Cells in a Row or Column
 
Join Date: Dec 2012
Posts: 3
Joe Ottenhof is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 12-18-2012, 01:46 AM
Kevin@Radstock Kevin@Radstock is offline Summing Non Contiguous Cells in a Row or Column Windows 7 32bit Summing Non Contiguous Cells in a Row or Column Office 2010 32bit
Office 365
 
Join Date: Feb 2012
Posts: 94
Kevin@Radstock is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 12-18-2012, 07:04 AM
Joe Ottenhof Joe Ottenhof is offline Summing Non Contiguous Cells in a Row or Column Windows 7 32bit Summing Non Contiguous Cells in a Row or Column Office 2007
Novice
Summing Non Contiguous Cells in a Row or Column
 
Join Date: Dec 2012
Posts: 3
Joe Ottenhof is on a distinguished road
Smile Thanks

Quote:
Originally Posted by Kevin@Radstock View Post
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,
Reply With Quote
  #4  
Old 12-18-2012, 07:08 AM
Joe Ottenhof Joe Ottenhof is offline Summing Non Contiguous Cells in a Row or Column Windows 7 32bit Summing Non Contiguous Cells in a Row or Column Office 2007
Novice
Summing Non Contiguous Cells in a Row or Column
 
Join Date: Dec 2012
Posts: 3
Joe Ottenhof is on a distinguished road
Default

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

Joe O
Reply With Quote
  #5  
Old 12-19-2012, 04:13 AM
Kevin@Radstock Kevin@Radstock is offline Summing Non Contiguous Cells in a Row or Column Windows 7 32bit Summing Non Contiguous Cells in a Row or Column Office 2010 32bit
Office 365
 
Join Date: Feb 2012
Posts: 94
Kevin@Radstock is on a distinguished road
Default

Hi Joe

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

Kevin
Reply With Quote
Reply

Tags
indexing, sum alternate cells

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Summing Non Contiguous Cells in a Row or Column Automatically enter date into a column and make that column read only Mr Davo Excel 1 10-29-2012 01:07 AM
Can I change the horizontal scrollbar to scroll smoothly rather than column by column carpat Excel 0 01-10-2012 09:34 AM
Summing Non Contiguous Cells in a Row or Column Construct a summary sheet by summing up from one or more than one sheet. PRADEEPB270 Excel 1 11-04-2011 03:46 AM
Count range cells eliminating merge cells danbenedek Excel 0 06-15-2010 12:40 AM
Summing Non Contiguous Cells in a Row or Column SUMMING TWO COLUMNS 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.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft