How To Sum Every Other Cell In Excel

Every now so you might want to sum every second, third, fourth, etc., cell in a spreadsheet. Now you can, with the following paragraphs.

Excel has no standard function that volition sum every nth jail cell or row. Even so, you can achieve this in a number of different means. All these approaches use the ROW office and the Modernistic role.

The ROW function returns the row number of a single cell reference:

ROW(            reference          )

The MOD function returns the remainder after number is divided by divisor :

Modern(            number          ,            divisor          )

Nest the ROW part within the Modern part (to supply the number statement), carve up it by 2 (to sum every 2d cell), and check to see whether the result is 0 (zero). If it is, the cell is summed.

You lot tin can use these functions in numerous ways-some of them producing meliorate results than others. For instance, an assortment formula to SUM every 2d cell in the range $A$one:$A$100 could look like this:

=SUM(IF(Modern(ROW($A$ane:$A$500),ii)=0,$A$1:$A$500,0))

Because this is an array formula, you must enter it by pressing Ctrl-Shift-Enter. Excel will add the curly brackets then that it looks like this:

{=SUM(IF(Modern(ROW($A$ane:$A$500),two)=0,$A$one:$A$500,0))}

You must permit Excel add these brackets, as adding them yourself will cause the array formula to fail.

Although this will practise the job, information technology is not good spreadsheet design to use this method. Information technology is an unnecessary use of an assortment formula. To make matters worse, it has the volatile ROW function nested within it, making the whole array formula volatile. This means the formula would constantly recalculate whenever y'all are working in the workbook. This is a bad way to get!

Hither'south another formula you can use, which is a slightly meliorate option:

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

You should, nevertheless, be aware that this formula volition return #VALUE! if any cells in the range contain text rather than numbers. This formula, although not a truthful array, as well will tedious downwardly Excel if besides many instances of it are used, or if those instances reference a large range.

Fortunately, in that location is a much better style that is not only more efficient, simply also far more than flexible. This requires using the DSUM function. For this example, nosotros used the range A1:A500 equally the range for which we demand to sum every nth cell.

Enter the word Criteria in cell E1. In cell E2, enter this formula:

=MOD(ROW(A2)-$C$2-1,$C$2)=0

Select cell C2 and so select Data » Validation. Select List from the Let: box, and in the Source: box, blazon: 1,ii,iii,4,five,6,seven,eight,9,10 . Ensure that the In-Cell drop-down box is checked and click OK. In cell C1, enter SUM every.... . In whatever cell after row 1, enter this formula:

=DSUM($A:$A,1,$East$ane:$E$2)

In the cell directly in a higher place where y'all entered the DSUM function, enter this:

="Summing Every " & $C$two & Cull($C$2,"st","nd","rd","thursday","th","th","thursday","th","th","th") & " Cell"

At present all you need to practise is cull the desired number from prison cell C2 and the DSUM role will do the rest.

As you tin can see from the figure, y'all tin employ one DSUM function to sum each cell at the interval yous specify. The DSUM function is a far more efficient formula than an array formula or the SUMPRODUCT function. Although setup tin take a piffling more time, information technology's actually a case of a niggling pain for a lot of proceeds.