<<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
---|

**Current Special!** Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download,**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.

**ROW Function**

Returns the row number of a single cell reference.

**Syntax: ROW(**reference**)**

**MOD****Function**

Returns the remainder after number is divided by divisor. The result has the same sign as the divisor.

**Syntax: MOD**(**number**,**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;

*=SUM(IF(MOD(ROW($A$1:$A$500),2)=0,$A$1:$A$500,0))*

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;

*{=SUM(IF(MOD(ROW($A$1:$A$500),2)=0,$A$1:$A$500,0))}*

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;

*=SUMPRODUCT((MOD(ROW($A$1:$A$500),2)=0)*($A$1:$A$500))*

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.

- Enter the word
in cell**Criteria****E1** - In cell
**E2**enter the formula:**=MOD(ROW(A2),$C$2)=0** - Select cell
**C2**and go to**Data**>**Validation**and selectfrom the**List**box and in the*Allow:*box type:*Source:***1,2,3,4,5,6,7,8,9,10** - Ensure the
is checked and click**In-cell dropdown****OK** - In cell
**C1**enter:**SUM every�.** - In any cell after row 1 enter this formula:
*=DSUM($A:$A,1,$E$1:$E$2)* -
**OPTIONAL:**In the cell directly above, where you have entered the DSUM enter;

* ="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. ALLpurchases totaling over $150.00 **gets you BOTH! Purchases MUST be made via this site**. Send payment proof to [email protected] 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...**