I found this partial solution to my problem:
Google Sheets auto increment column A if column B is not empty
With this formula:
=ARRAYFORMULA(IFERROR(MATCH($B$2:$B&ROW($B$2:$B),FILTER($B$2:$B&ROW($B$2:$B),$B$2:$B<>""),0)))
What I need is the same but instead of continuous numbers I’d need it to restart incrementing from 1 at each new category string on an adjacent column (column A in example below, categories strings are A, B, C, D etc.).
For example:
Problem with formula in C12 and C15 (added numbers 1 and 2)
Needed result in column D, as with D11 and D19 restarts incrementing from 1 at new category string)
| 1 | needed result | |||
|---|---|---|---|---|
| 2 | A | 1 | 1 | 1 |
| 3 | A | |||
| 4 | A | |||
| 5 | A | 1 | 2 | 2 |
| 6 | A | |||
| 7 | A | |||
| 8 | A | |||
| 9 | A | 1 | 3 | 3 |
| 10 | A | |||
| 11 | B | 1 | 4 | 1 |
| 12 | B | 1 | ||
| 13 | B | |||
| 14 | C | 1 | 5 | 2 |
| 15 | C | 2 | ||
| 16 | C | |||
| 17 | C | 1 | 6 | 3 |
| 18 | C | |||
| 19 | D | 1 | 7 | 1 |
| 20 | D | |||
| 21 | D | |||
| 22 | D | 1 | 8 | 2 |
| 23 | D | |||
| 24 | D | 1 | 9 | 3 |
| 25 | D | |||
| 26 | D | |||
| 27 | D | 1 | 10 | 4 |
| 28 | D | |||
| 29 | D |
>Solution :
try:
=INDEX(IF(B2:B="",,COUNTIFS(A2:A&B2:B, A2:A&B2:B, ROW(A2:A), "<="&ROW(A2:A))))
or:
=INDEX(IF(B2:B="",,COUNTIFS(A2:A&IF(B2:B<>"", 1, ), A2:A&IF(B2:B<>"", 1, ), ROW(A2:A), "<="&ROW(A2:A))))
