Nth Occurrence is one of my favorite topic in Google Sheets. In earlier posts, I’ve detailed about finding nth Occurrences of a lookup value in Vlookup and also nth Occurrences of a Date in a month. Now here let me explain to you how to copy every nth Cell in a column in Google Sheets.
To do this, I am using Google Sheets MOD function. You can find the links that point to the above said other two tutorials at the end of this post.
What’s Meant by Every nth Cell in a Column in Google Sheets?
Here every nth cell means the repetition of a specific number of cells. If your nth number is 5 then it would be like the 5th cell, 10th cell, 15th cell and so on. That means ‘N’ is a user specified number to repeat in rows.
Formula to Copy Every nth Cell in a Column in Google Sheets
In the real sense, I can’t give a formula without knowing the nth cell number you want in the formula.
So in this formula, I am using every 4th cell as my nth cell. Also, I consider Column A in this example. you can change the nth cell and column reference later.
Master Formula:
=ArrayFormula(if(ArrayFormula(mod(row(A1:A25),4))=0,A1:A25,))
See the below screenshot to understand what this formula returns.
I have the above formula entered in Cell B1. So the formula copies the values from Column A that found in every 4th row like row # 4, 8, 12 and so on.
You can change the nth row in the formula by only changing the number 4 in it. Changing the number from 4 to 6 copies the values from every 6th rows.
Should I enter this formula in the first row itself?
No! You can insert this formula in any row. It will correctly copy every nth row in the column.
Can you explain how this formula finds every nth row value in Google Sheets?
The secret lies in the MOD function.
Formula Breakup 1:
=ArrayFormula(mod(row(A1:A25),4))
This MOD array formula would return the value 0 in every fourth rows. Using an IF logical test you can copy the value of every nth (here 4th) cell. That is what I did. If you want, I can explain it further. See how If copies values from a column if the cell value is 0.
The below IF based formula would return the value in the range A1: A25 if any of the cells contains the value 0.
=ArrayFormula(if(A1:A25=0,A1:A25,))
In this formula, you only want to change the first A1: A25 range with the MOD formula (Formula Breakup 1).
Actually, the MOD formula acts as the virtual column A that has the value 0 in every nth row. That’s all. This way you can copy every nth cell in a Column in Google Sheets.
Similar:
Prashanth,
Formula 1 worked perfectly! I can’t thank you enough for helping me with this! No article, self-help guide, YouTube video could help me with this, but YOU came through! MVP!
Thank you so much for this Prashanth!
I’ve been trying to find a way to automatically add every 6th column cell from the 9th row beginning at E9 (So E9, K9, Q9, etc). Can you help me with this? After a full day of trying to figure this out I’m about ready to throw my laptop out of the window…
These cells don’t have nor need references. They’re fully independent cells.
Additionally, new columns are regularly added. If possible I’d like this formula to also automatically add the new columns as they’re added.
Thank you!
Hi,
Already posted a solution.
https://infoinspired.com/google-docs/spreadsheet/sum-every-n-cells-to-the-right-or-bottom-google-sheets/
But I will try to customize it for your range and update you via my next comment.
In the meantime, you can try it using my tips on the linked post.
Just wait! I’ve gone thru’ your comment the second time. Please ignore my previous reply as it’s not what you are looking for. I will update you soon!
Formula 1:
=sum(ArrayFormula(if(mod(sequence(1,COLUMNS(E9:9)),6)=1,E9:9)))Formula 2:
=ArrayFormula(sumif(mod(sequence(1,COLUMNS(E9:9)),6),1,E9:9))Try either of the ones. Both formulas are working in my test.
How might one go about deleting every nth row (the entire row and not just it’s content) from the worksheet? So as in your example, deleting every 4th row would result in the series 5, 5, 5, 4, 4, 4, 3, 3, 3, etc. without any blank cells in between.
Hi, Johmann,
Maybe you can do that with a script. Unfortunately, I am not familiar with that 🙁
Here is my workaround using formula.
Delete Every Nth Row in Google Sheets Using Filters