Excel has no standard Function that will allow us to SUM every nth cell. 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
**Criteria**in cell**E1** - In cell
**E2**enter the formula:**=MOD(ROW(A2),$C$2)=0** - Select cell
**C2**and go to**Data**>**Validation**and select**List**from the**Allow:**box and in the**Source:**box type: 1,2,3,4,5,6,7,8,9,10 - Ensure the
**In-cell dropdown**is checked and click**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.

**Keywords**: Sum every 2nd cell row, SUM every nth, SUM every other cell row