Google Sheets – Formula to Count Up to 12 and Restart

formulasgoogle sheets

I have columns under names of people, the rows correspond to dates below the name. I also have another row with a counter. Something like this:

Bob
Counter 2
12 MAR X
11 MAR
10 MAR X

The counter adds +1 if the field below is not empty. (In this case it is X). What I am trying to do is get this counter get up to 12 and then start counting again from 1 up to another 12.
This is what I came up with:

= IF(MOD(COUNTA(indirect("B4:B"));12)>0; COUNTA(indirect("B4:B"))- 12*QUOTIENT(COUNTA(Indirect("B4:B"));12);COUNTA(indirect("B4:B")))

It is very clumsy, and besides tied to a specific column, in other words because of the quotes I can move them freely. Does anybody know how to make it easier?

Best Answer

It looks like you can just use =MOD(COUNTA(B4:B),12):

enter image description here

(those are 15 X-es, and 15 % 12 = 3)