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.
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.
- 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