In my Google Sheet, I have 1000 rows of Date entries. For each Date, I am calculating the Month# and Week# using MONTH() and WEEKDAY() functions respectively. Here is the link to a sample file:

CodePudding user response：

Delete everything from Column F (including the F2 header). Then place the following formula into cell F2:

=ArrayFormula({"Output";IF(C3:C="",,IFERROR(C3:C-VLOOKUP(E3:E,{E3:E,C3:C},2,FALSE)))})

This one formula will create the header and return results for all valid rows.

Since VLOOKUP always finds only the first matching instance of what it is looking up, we can use it to ask that each value in C3:C subtract that first instance of where month-number-and-week-number match for each row.

By the way, although you didn't ask about this, you can also use this type of array formula in Columns D and E, instead of all of the individual formulas you have. To do that, delete everything from Columns D and E (including the headers). Then...

Place the following formula in D2:

=ArrayFormula({"Month #";IF(B3:B="",,MONTH(B3:B))})

... and the following formula in E2:

=ArrayFormula({"Week #";IF(B3:B="",,WEEKNUM(B3:B))})