In Calc, how do I conditionally reset a count within a column?

Here’s the situation: groups of lines of data (in this instance, appearing on alternate rows; however, sometimes the rows are odd; sometimes they’re even; there’s currently an empty row between data sets, but that may disappear).

I need a way of resetting the “$” line-number for a count-function that in Cell AO5 looks like this:


This keeps all my data strings in Column AO the same length. The important part is simply “COUNT(AP$5:AP7)” and the “P” prefix is a code for the type of data reported in the count.

Imagine a straight fill down Column AO: the count increments up, as expected, until the fixed starting point is manually reset to a new line. Counts currently vary between 1 and 27, and it’s a serious pain to manually reset this every time: I’m not very far into this, and already I’ve got about 9300 lines of data. GRRRR!

Let’s imagine the first data set is contained within the range AP5:AP11, followed by a data set within the range AP13:AP19, and so forth.

I’m hoping I can just (at least for now) put in a “reset command” column: when, for example, cell AN13 contains “RESET” — or if a “reliably filled” stack such as in column R is suddenly blank (column AP contains “empty” cells as well as cells being counted), then — the fill automatically changes in Cell AO13 to:


I understand that there needs to be some sort of discriminator between the “=” and the “IF” in the above examples — perhaps I can do all this by using a nested “IF” function, but I don’t know the syntax for generating a discriminator that will work as I need.

I tried starting with


which doesn’t work:

  1. When the adjacent cell (on the same line) in Column AN = “RESET”, it returns “P00” — which would be hilarious if I was in kindergarten, but it isn’t getting even a smile from me, right now; worst-case, it should at least alternate between 1 and 0, so I have no idea what’s going on, there.
  2. Any time the adjacent cell (on the same line) in Column AN doesn’t = “RESET”, the count begins from Cell AP5.

I don’t know how to (non-manually) integrate recurring anchors (the “$” cells) into the formula.