Increment number in array formula based on a condition

Question

I have a data set with Sub-Total rows at various intervals. As the data can have rows inserted or deleted, I am trying to use an array formula to give each group of data an incremented integer as a reference number.

Example Data:

Column A:  
x  
x  
Sub-Total  
x  
x  
x  
Sub-Total  

When using a filled-down formula I can place '1' in B1, then =if(A1="Sub-Total",B1+1,B1) in cells B2 and down. This yields the following in column B:

1  
1  
1  
2  
2  
2  
2  

I have tried:

=ArrayFormula(IF(A1:A="Sub-Total",B1:B+1,B1:B)) 

But the FALSE condition does not seem to reference the row above and shows as blank.

I have also tried using OFFSET(), but with no luck.

Answer 1

• for "sub-total" count you can use:

  =ARRAYFORMULA(IF(A2:A="Sub-Total", COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A)), ))