0

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.

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

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

    0

  • Thanks, but I was after the number to appear next to every row per group of data. This is so that I can use it to SIMIFS() or COUNTIFS() per group. – Stuart Ingram May 15 at 3:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.