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
Patrick2788's avatar
Patrick2788
Silver Contributor
Mar 27, 2025

This is a solution for Excel 365:

=LET(
    TotalDays, LAMBDA(each_row,
        LET(
            k, COUNTIF(each_row, "DO"),
            split, TEXTAFTER(CONCAT(each_row), "DO", -1, , , ""),
            IF(k >= 3, LEN(split), "")
        )
    ),
    BYROW(grid, TotalDays)
)

 

Grendalan's avatar
Grendalan
Copper Contributor
Mar 27, 2025

Hey Patrick,

Thank you for the quick response, I tried to implement your formula and sometimes it is correct, others it is off, so not sure where it is going wrong?

 

I notice that if I go through and change values to change the count then start from 0 days off then go back and change values it seems to count correctly after that

 

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Mar 27, 2025

    Do you have a sample workbook you're able to share?

    • Grendalan's avatar
      Grendalan
      Copper Contributor
      Mar 31, 2025

      Sorry Patrick,

      Was on days off there.

      Please find attached an example book, ask if you have any questions

      • m_tarler's avatar
        m_tarler
        Bronze Contributor
        Apr 01, 2025

        so I see this example is based off my file and formula so I made some tweaks to help.

        a)  I updated the month header to use 'center across' instead of merged cell.  this was partly because I don't like merged cells but also so I could add a date and labels in AG1 and AH1

        b) I updated the test date ("tday") in the formula to use the cell AH1.  you could set this cell to =TODAY() but in this test file you need to see how the formula works and it isn't full up to today.

        c) I use a weird date in the December tab because the way this "test date" works is it assumes you have data for each day up to that date and simply takes the corresponding # of columns, but because Feb only has data upto the 10th and then it skips to December I entered the Day 2/20 (10 days in Dec tab added to the 10 days in Feb)

        and as a bonus I gave you a new formula for your YEAR TO DATE column

        hope this is helpful

Resources