Forum Discussion

Grendalan's avatar
Grendalan
Copper Contributor
Mar 26, 2025

Formula to count a row, reset if 3 values found within last 24 days, display count since reset

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Since Reset F F F F DO DO F F F DO F F DO F F F 3 So using the basis of the table above, I am trying to come up with ...
excel
Formulas and Functions
Grendalan's avatar
Grendalan
Copper Contributor
Apr 02, 2025

Hey There m_tarler,

Thanks for the reply,

Testing this out and seeing you have absolute cell's referenced and wondering if I can change that to relative columns and rows as I may need to add names or delete names to the different months as my personnel count changes

 

I can also remove the Month on the top row as I have the pages labeled by month so I can move it to a different cell so it will only be the numbered days and name in the top row

 

Let me know.

 

Thanks.

m_tarler's avatar
m_tarler
Bronze Contributor
Apr 03, 2025

As for the header that is fine. I just tried to add that "test date" up there and couldn't because it was a merged cell.  So I just changed it from a merged cell to use 'center across cells' formatting instead.

As for a more dynamic range of rows, I see a couple options.  You could take a range larger than you could ever expect to have (e.g. go to row 999) and trim it based on the count on that page:

=LET(all_data,TAKE(HSTACK(January:December!C2:AG999),COUNTA(A:A)),
      window,24,
      tday,DAYS(AH1,DATE(YEAR(TODAY()),1,1))+1,
      data,TAKE(DROP(FILTER(all_data,TAKE(all_data,1)<>""),1),,tday),
      seqA, SEQUENCE(tday),seqB, SEQUENCE(,tday-window+1),winMatrix, (seqA >= seqB)*(seqA <= (window-1 + seqB)),
      results, (MMULT(--(data="DO"),winMatrix)>=3)*DROP(data="DO",,window-1)*(window-1 + seqB),
      out,BYROW(results, LAMBDA(r, tday-MAX(r))),out)

or you could make the formula for only ONE line and fill down as needed:

=LET(header_data,HSTACK(January:December!$C$2:$AG$2),
      line_data,HSTACK(January:December!$C3:$AG3),
      window,24,
      tday,DAYS($AH$1,DATE(YEAR(TODAY()),1,1))+1,
      data,TAKE(FILTER(line_data,header_data<>""),,tday),
      seqA, SEQUENCE(tday),seqB, SEQUENCE(,tday-window+1),winMatrix, (seqA >= seqB)*(seqA <= (window-1 + seqB)),
      results, (MMULT(--(data="DO"),winMatrix)>=3)*DROP(data="DO",,window-1)*(window-1 + seqB),
      out,BYROW(results, LAMBDA(r, tday-MAX(r))),out)

in the attached I show them in Jan and Feb tabs respectively.

NOTE I found a slight error of sorts in that this formula is relying on the row 2 with the month days listed to filter out the blank columns in that range.  So Feb should have only had days up to 28 AND all those days should be filled in order to make the next tab (Dec in this case) correct.  

Resources