Search code examples
google-sheets-formula

Reset count after a new month starts

I'm adding data to a spreadsheet and I want to do basic tasks programmatically.

Every time I add a date like 03/01/2022 the month cells updates to "March" with

ARRAYFORMULA(IF(E2:E = "","", TEXT(E2:E,"mmmm")))

So, I'm counting the entries per month like this:

enter image description here

I created a formula to make a sequence, but it'll go infinitely as per the number of rows, I'd like to reset the count when the Month cell is different than the previous one.

=SEQUENCE(ROWS(B2:B))
Solution

  • David, I assume "Month" is in column B and you want the sequence in column A under "No."

    Try using this formula in A2:

    =arrayformula(if(B2:B="",,countifs(B2:B,B2:B,row(B2:B),"<="&row(B2:B))))

    Briefly:

    • uses the arrayformula so you don't have to copy down the formula

    • if(B2:B="",, takes care of any blanks

    • countifs() along with row() does the rest of the magic.

    • to see the role of row(), try using just countif(B2:B, B2:B). This will give the total number of occurrences of "January. "February", etc.

    • row() combined with "<="&row() makes sure that the formula counts occurrences above the current row only.

    Watch out for year change. All "January" values across different years will be added to the sequence.

    Good luck.