Google spreadsheet: relative rows numbering based

Question

In this former post, Taosique kindly provided a solution to make numbering cell empty on ONE condition, skip numbering for this cell, and take it back on subsequent relevant cells, in a way that rows can be moved around and the (relative) numbering adapted.

Basically, if adjacent B cell = Value 3, make A cell empty, and skip numbering; then take back relative numbering.

| Row nbr |  B col. | [Explanation]                       |
|---------|:-------:|-------------------------------------|
| R01     | Value 1 |                                     |
| R02     | Value 1 |                                     |
| R03     | Value 2 |                                     |
|         | Value 3 | Col. B value = 3 => Col. A is empty |
| R04     | Value 2 | Numbering takes up                  |
| R05     | Value 1 |                                     |
|         | Value 3 | Col. B value = 3 => Col. A is empty |
| R06     | Value 2 | Numbering takes up                  |
| ETC.    |         |                                     |

This works great:

=IF(INDIRECT("B"&ROW())="Value 3","",ROWS(INDIRECT("B"&10&":B"&ROW()))-COUNTIF(INDIRECT("B"&10&":B"&ROW()),"Value 3"))

..as can be seen on the example spreadsheeet.

Now I need to take this one step further, and apply that same logic with multiple (OR) conditions, so numbering gets skipped if B cell value = "Value 3" OR "Value 2" (not just "Value 3").

I tried adapting the formula here:

=IF(OR(INDIRECT("B"&ROW())="Value 3";INDIRECT("B"&ROW())="Value 2";"";ROWS(INDIRECT("B"&10&":B"&ROW()))-COUNTIFS(INDIRECT("B"&10&":B"&ROW());"Value 3";(INDIRECT("B"&10&":B"&ROW());"Value 2"))))

The first OR condition seems ok ; I switched COUNTIF to COUNTIFS, so I could use multiple criteria, but this gives an error. Maybe because I'm mixing COUNTIFS and INDIRECT? Because even this last part alone throws an error:

=COUNTIFS(INDIRECT("B"&10&":B"&ROW());"Value 3";(INDIRECT("B"&10&":B"&ROW());"Value 2"))

Sorry if I'm missing the obvious.

Answer 1

The combined formula would be long enough that it would become difficult to maintain and update in the future. The problem is that you are generating the same query twice.

I recommend instead inserting a new column at B (which would push the current column B to C), and then writing a formula such as =IF(OR(C2="value 2",C2="value 3"),"skip","")

Then, for clarity's sake, change "value 3" in your original formula to "skip", and (if you wish) hide the helper column B. This would give you a formula far easier to maintain, and you can make the boolean condition in column B as complex as you like without significant difficulty.