Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Copy every or each Nth cell from Row or Column

Copy and group every Nth cell or each N-th value

This tutorial describes the various ways of copying every Nth cell in a kind of continuous intervals. If you are any of these terms under then continued to read the tutorial.
  • How to copy each Nth third or fourth cell in column
  • How to copy every fifth cell of the order
  • How to display the value or data every seven or ten cells
  • Copy every Nth value

How to copy each Nth or fifth (third, seventh, tenth, etc ...) cell from the same row

In this first example, see the situation in the picture below. To enlarge the image click on the image.

How to copy each Nth cell in the same Row
Situation in the picture above is as follows. In the second and third rows have values (data) that we want to copy or provided. In this case, we want to copy each fifth value (or cell) from the ranks. With this copy of each 5-cell and automatically we group all of the displayed values. Here are some formulas that display every Nth value

The formula in cell C5 is as follows: (Formula copy to the right). This formula copies the first value of the cell in the image 'G2' indicated in red.


=IF(OFFSET($A2;;COLUMN(A:A)*5+1)=0;"";OFFSET($A2;;COLUMN(A:A)*5+1))

The formula in cell C6 is as follows: (Formula copy to the right). This formula copies the first value that is in the cell indicated by 'L3' in the image in red.

=IF(OFFSET($A3;;COLUMN(B:B)*5+1)=0;"";OFFSET($A3;;COLUMN(B:B)*5+1))

In the formulas above you notice that they are different in that the first formula started in column 'A' and another formula started in 'B' column.

The formula in cell C8 is as follows: (Formula copy to the right). This formula copies the first value, which is located in the first cell 'C2'.

=OFFSET($A2;;COLUMN(A:A)*5-3)

The formula in cell C9 is as follows: (Formula copy to the right). This formula copies the first value, which is located in the first cell C3.

=IF(OFFSET($A3;;COLUMN(A:A)*5-3)="";"";OFFSET($A3;;COLUMN(A:A)*5-3))

An example of how to SUM every Nth value from the same row
If you need a SUM value of each N-th cell in the same row, you use this formula below. The formula below summarizes every third value in the same row, the first value is in cell 'C2'.

The formula in cell C11 is the following:

=SUMPRODUCT(--(MOD(COLUMN(C2:AG2);3)=MOD(COLUMN(C1);3));(C2:AG2))

Copy every four Nth value, or cells from the same column

In this second example in the image below, you see how to copy each Nth value or a cell in the same column. In this case, we want to copy every fourth value or cell. There are several ways to solve this problem, see the formulas below.

Display each Nth cell from Row or Column

The formula in cell C2 in the image above is as follows: This formula shows the value of the first cell from which you we want to return Nth value. This formula serves as a precursor to the following formula below to C3.

=A7

The formula in cell C3 in the image above is as follows: Note in the formula 4, this number is every fourth cell.

=OFFSET($A$7;ROW(A1)*4;0)

The formula in cell H2 in the image above is as follows: You notice in the formula number 4, this number is every fourth cell, range A7:A25 determines the position of the first N-cells and we want to display.

=INDEX($A$7:$A$25;4*(ROWS($A$2:A2)-1)+1)

The formula in cell J2 in the image above is as follows: The formula as the first cell returns the value from A2. You notice in the formula the number 4, this number is every four cell range ROWS(J$1:J1) determines the Column and Row in which there is this formula.

=INDEX(A$1:A$21;4*ROWS(J$1:J1)-2)

The formula in cell L2 in the image above is as follows: This formula displays the same result as the previous one. The formula as the first cell returns the value from A2. You notice in the formula the number 4, this number is every four cell range ROW(A1)-1 determines the row in which there is this formula.

=IF(A2>0;OFFSET($A$2;(ROW(A1)-1)*4;0);A2)

How to display or copy every third or Nth value, or a cell from Excel column

In this example, I'll put a few formulas author 'cpearson' by which you can copy each Nth value from column. You notice it in the picture below.

Display or copy every Nth value or cell from Column
ARRAY formula in cell F2 in the image above is as follows: This formula displays the first value we want to copy from column 'A'. You note that the formula use absolute cell address, the cell that contains the formula. Number 3 in the formula means to copy every third value in column A.

=IF((ROW()-ROW($F$2))*3>=(ROWS($A$2:$A$21));"";OFFSET($A$2:$A$21;(ROW()-ROW($F$2))*3;0;1;1))

ARRAY formula in cell H2 in the image above is as follows: This formula displays the first value you want to copy from column 'A'. You notice the formula uses absolute cell address, which contains the formula. Number 3 in the formula means to copy every third value in column A. Number 1 (marked in red) indicates the number of the starting row in which the first cell you want to copy.

=IF((ROW()-ROW($H$2))*3>=(ROWS(OFFSET($A$2:$A$21;1-1;0)));"";OFFSET(OFFSET($A$2:$A$21;1-1;0);(ROW()-ROW($H$2))*3;0;1;1))

If you want to count how many times it occurs every third cell in a certain range of cells then use the following formula in column J.

The formula in cell J2 in the image above is the following: The formula in J10 the function SUM.

=COUNTIF(OFFSET($A$2;(ROW(A1)-1)*3;0);">=1")

If you want to sum up every third cell with the displayed values then use the following formula in column 'K'.

The formula in cell K2 in the image above is as follows: The formula displays each third cell and the value of the the respective cell contains. The formula in cell K10 is the SUM function.

=INDIRECT("A"&(ROW(A1)-1)*3+2)

If you want to display a single cell SUM of all values from each of the third cell in column 'A' then use the following formula.

ARRAY formula in cell D2 in the image above is as follows: Number 1 highlighted in red is the row start and the number 3 is the interval for each third cell.

=SUM(IF(MOD(ROW(OFFSET(A2:A21;1-1;0))-ROW(OFFSET(OFFSET(A2:A21;1-1;0);0;0;1;1));3)=0;OFFSET(A2:A21;1-1;0);0))

Another way SUM each of the third cell in the column is in the cell 'D3'.

ARRAY formula in cell D3 in the image above is as follows:

=SUM(IF(MOD(ROW($A$2:$A$21)-ROW(A2);3)=0;$A$2:$A$21;0))

The average sum of the values of each of the third cell in column

If you want to calculate the average of the sum of all values every Nth cells then use the following formula (author of formula cpearson.com). Number 1 highlighted in red is the row start and the number 3 is the interval for each third cell.

=AVERAGE(IF(MOD(ROW(OFFSET($A$2:$A$21;1-1;0))-ROW(OFFSET(OFFSET($A$2:$A$21;1-1;0);0;0;1;1));3)=0;OFFSET($A$2:$A$21;1-1;0);FALSE))