Google-sheets – How to reference multiple rows every nth row

google sheets

In Google Sheets, I have a set of data in column A1 to A100. In column B, I would like to reference three rows from column A while skipping every other two rows, like this:

Column A
1
2
3
4
5
6
...
100

Column B
3
4
5
8
9
10
...

How do I do that? I can use the offset function to skip every nth row, but I can only make it display one row at a time, when I actually want three rows at a time.

Best Answer

You apparently want to skip rows 1,2, 6,7, 11,12, 16,17, 21,22 and so on. Chances are that the data in these rows differs somehow from the data in the rest of the rows, and the easiest solution would probably be to filter by that data rather than blindly filtering by row numbers.

Nevertheless, here is a formula that filters by row numbers the way you describe:

=filter(A1:A, regexmatch(trim(mod(row(A1:A), 5)), "3|4|0"))

See filter() and regexmatch(). To learn the exact regular expression syntax used by Google Sheets, see RE2.

Here are a couple additional patterns to choose which rows to show.

Only show rows 5, 10, 15 and 77:

=filter(A1:A, regexmatch(trim(row(A1:A)), "^(5|10|15|77)$"))

Show all rows except rows 5, 10, 15 and 77:

=filter(A1:A, not(regexmatch(trim(row(A1:A)), "^(5|10|15|77)$")))

Only show rows with odd row numbers 1, 3, 5, 7...:

=filter(A1:A, isodd(row(A1:A)))

Only show rows with even row numbers 2, 4, 6, 8...:

=filter(A1:A, iseven(row(A1:A)))

Show every third row starting from row 2:

=filter(A1:A, (row(A1:A) - 2) / 3 = int((row(A1:A) - 2) / 3))

Show rows 4,5, 9,10, 14,15, 19,20, 24,25...:

=filter(A1:A, 
  ((row(A1:A) - 4) / 5 = int((row(A1:A) - 4) / 5)) 
  + 
  ((row(A1:A) - 5) / 5 = int((row(A1:A) - 5) / 5)) 
)
Related Topic