Excel worksheet have number of rows and columns. Intersection of row and column is known as a “cell”. We insert data in these cells only. Every cell has got a row number and a column number. We have to add value of every nth cell in a row or column. In simple words if we have got data in order 10 20 30 40 50 60, in cells A1 B1 C1 D1 E1 F1, and we need to add the totals of B1 D1 F1. Simplicity says that we apply “=B1+D1+F1” as formula, and get our answer. Now imagine our data is from A1 Cell to BB1 cell (total 54 cells), so writing “=B1+D1+F1+H1+J1……….+BB1” is very cumbersome; agreed we will get right answer, but following is a simple formula which can be used to add every nth cell in n number of cells

• Divide all cells of a contiguous range from which we have to add value of every nth cell by n.
• Compare the remainder of first cell to be added with remainder of all cells.
• Instruct excel to add all cells with common remainder as that of first cell to be added.

Suppose we need to add every 3rd row i.e. cells in row 3, 6, 9, 12 and so on. Here, if we divide all row number of cells to be added with 3, we shall get common remainder of 0.

If data to be added is in range “A1:A20” and we need to add data in every 3rd row, put below formula in target cell to get required result

=SUMPRODUCT((MOD(ROW(A3),3)=MOD(ROW(A1:A20),3))*A1:A20)

As we have to add every 3rd row, we choose 3 as divisor. Using “MOD” and “ROW” functions we are comparing remainder of first cell to be added i.e. “A3” with other cells. “SUMPRODUCT” will add data of all cells having same remainder as that of first cell i.e. “A3”.

If your data is in single row instead of single column replace “ROW” function with “COLUMN” to get desired result.

E.g. If your data is in “A1:BB1” cells then the formula would be:

=SUMPRODUCT((MOD(COLUMN(A3),3)=MOD(COLUMN(A1:BB1),3))*A1:BB1)