Current Special! Complete Excel
for Excel 97 - Excel 2003, only
30 Day Money Back Guarantee & Free
Excel Help for LIFE!
Got any Excel Questions? Excel Help
SUM Every Nth Cell
See Also: Excel Subtotals || Making the SUBTOTAL Function Dynamic || Bold Excel Subtotals Automatically || Sum Every Nth Cell || Count of Each Item in a List || Excel Pivot Tables
Surprising at it may seem, it is not as uncommon as you may think for an Excel user to want to sum every 2nd, 3rd, 4th etc cell in a spreadsheet. Excel has no standard Function that will do this. However, it can be done it a number of different ways. All these ways make use of the ROW Function and the MOD Function.
Returns the row number of a single cell reference.
Returns the remainder after number is divided by divisor. The result has the same sign as the divisor.
We nest the ROW function within the MOD function (to supply the number argument), divide it by 2 (to sum every second cell) and check to see if the result is 0 (zero). If it is, then the cell is summed.
Let's look at just how we can use these. Some ways are better than others. By this I mean, we can use an array formula , but arrays are best suited to referencing either, single cells or small ranges. Having too many arrays that reference a large range will slow down Excel's recalculation time. I have seen many Workbooks that have become unusable due to array formulae. An array formula to SUM every 2nd cell could look like;
As this is an array formula it must be entered by pushing Ctrl+Shift+Enter and then Excel will add the curly brackets so it looks like;
You must let Excel add these. Adding them yourself will cause the array formula not to work.
While this will do the job, it is not good spreadsheet design to do so. The reason is, it is an unnecessary use of an array formula and to make matters worse, it has the Volatile ROW Function nested within it, making the whole array formula Volatile. This would mean the formula would be constantly recalculating whenever you are working in the Workbook. In a nutshell it is bad way to go!
Another formula we can use, which is a slightly better choice, than the above array formula, is;
You should however be aware that it will return #VALUE! if any cells in the range contain text. This formula, while not a true array formula, will also slow down Excel if too many are used and/or they reference a large range.
Ok, enough of how we shouldn't do this, lets look at a much better way that is not only more efficient but also far more flexible. For this we will use the DSUM function. For the example I will use the range A1:A500 as the range we need to sum every nth cell.
="Summing Every "&$C$2&CHOOSE($C$2,"st","nd","rd","th","th","th","th","th","th","th") & " Cell"
Now all you need to do is choose the desired number from cell C2 and the DSUM will do the rest.
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to firstname.lastname@example.org 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates