Question: At work we are tracking a certain event that is suposed to occur once a week for each of our customers. A sample of the sheet we use to track this can be seen here:

<https://docs.google.com/spreadsheets/d/128JhxL1gv2vTF027zkjYDGp48MIQ5-q9F9B6bIltCfM/edit#gid=0>

I'm trying to create an index that counts how many events occurred in the last 4 weeks for each customer (i.e cells that contain "OK") but I'd like it to be updated as the weeks go by without me having to change the range in the formula every week. Any ideas? Thanks!

Edit: the event we are tracking is related to the COVID context so we can't be sure for how many weeks we'll keep tracking it. There are 8 weeks in the sample, but we may add ore over time

Answer: 
use:

```
=ARRAYFORMULA({Sheet1!A2:A, IF(Sheet1!A2:A="",, 
 MMULT( N(IFERROR(INDIRECT("Sheet1!"&ADDRESS(2, MATCH("WK"&MAX(IF(
 (LEFT(Sheet1!1:1, 2)="WK")*(Sheet1!2:2<>""), COLUMN(Sheet1!1:1), ))-3, 
       Sheet1!1:1, 0), 4)&":100")="OK", 0)), 
 TRANSPOSE(N(LEFT(INDIRECT("Sheet1!"&ADDRESS(1, MATCH("WK"&MAX(IF(
 (LEFT(Sheet1!1:1, 2)="WK")*(Sheet1!2:2<>""), COLUMN(Sheet1!1:1), ))-3, 
       Sheet1!1:1, 0), 4)&":1"), 2)="WK"))))})

```

[![enter image description here](https://i.stack.imgur.com/deFGw.png)](https://i.stack.imgur.com/deFGw.png)
If you want to expand the weeks it might be a good idea to do the counting in a second tab. In the tab 'JPV' I entered in cell B2

```
=Arrayformula({Sheet1!A2:A, if(len(Sheet1!A2:A), mmult(N(Sheet1!B2:100="OK"), transpose(N(left(Sheet1!B1:1, 2)="WK"))),)})

```

See if that could work for you ?

UPDATED

Assuming you only add new weeks (headers) when the data (from row 2 and further down) for those weeks will be added to the table, you could try

```
=Arrayformula({Sheet1!A2:A, if(len(Sheet1!A2:A), mmult(N(Sheet1!B2:100="OK"), transpose( (left(Sheet1!B1:1, 2)="WK") * (substitute(Sheet1!B1:1, "WK",)+0 > MAX(substitute(Sheet1!B1:1, "WK",)+0)-4))),)})

```