Google Sheets formula to track number of event occurrence in the last 4 weeks

user_13569615 0
Viewed 160

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

array-formulas google-sheets match count arrays
2 Answers

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

user_56326290

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))),)})
user_38894230
Related