Sum of Every nth cell in Row or Column

by | Jun 21, 2020 | Formula & Functions, Sum & Count

Goal:

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

Algorithm:

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

Formula:

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)

Explanation:

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

Tip:

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)

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

By Rajesh K Saraogi

Rajesh is a hardcore finance person with a penchant for latest technology. His passions are latest technology and to improve the productivity with simple, yet powerful software – mainly on Microsoft Excel platform. He is a qualified MCA. Whenever he gets time he loves to blog, to share the knowledge, which is the only thing which increases after sharing!

Share This

Share this post with your friends!