Increase 1 increment every x rows on Google Sheets

Question

Basically, I just want to automatically increase the week number (on column A), and have it correspond with every new Monday (in Column B). I have been trying to click and drag, hoping it would somehow automatically adjust the increment every 7 rows but it doesn't seem to work.

How would I be able to do this without having to manually increase the number every Monday?

Answer 1

Formula

=ArrayFormula(IF(WEEKDAY(FILTER(C:C,C:C>0),2)=1,WEEKNUM(C:C,1),))

Explanation

WEEKNUM(C:C,1) returns the number of the week for all the values column C. To to fill only the cells that corresponds to mondays and that row is not empty, it was included as the TRUE result of an IF function with the following test:

WEEKDAY(FILTER(C:C,C:C>0),2)=1

ArrayFormula makes that the results be expanded to the required rows.

Answer 2

1. Write a 0 on cell A1
2. Insert the following formula on cell A2 =A1+IF(WEEKDAY(C2,2)=1,1,0)
3. Copy cell A2 and paste on desired rows below

Answer 3

A generic counter (does not rely on any other column) for ColumnA for example:

=if((rows($1:1)-1)/7=int((rows($1:1)-1)/7),max(A$1:A1)+1,"")

to, when copied down, place an incrementing integer every eighth row without displaying in intervening cells. Assumes a label (or a blank cell) in A1, but a value could be entered there so that the incrementing starts from it.

Change the 7s to say 12s for example and the values would be spaced a further five rows apart.

Can be placed in any row and copied down but rows above should be blank or text and the row number in A1 adjusted to the row number immediately above that chosen for the formula.

Answer 4

Another option, copy this into a cell and drag it down the column:

=CEILING((ROW(1:1)-ROW($1:$1))/(7))

• 7 is the number of times you want the count to repeat each increment.
• Swap out the '"1's" with the row number you start this in.
• The first result of this function will return "0". So just delete the first cell when you copy it if you don't want that.

Note: this doesn't leave blanks.