Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

How to Sum Every Second, Third or Nth Row or Cell in Excel

| | Information Helpful? Why Not Donate.

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

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)

MODFunction

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.

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 [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

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