restart numbering based on formula

[melvinkoshy]

I have set up a sheet with automatic numbering based on formula based on “flag” field.

Flag “h” is for headings

Flag “”m” and “s” are for Sl. No. field
If flag is “m”, then the Sl. No corresponding to that item is incremented by 1
If flag is “s”, then the Sl. No is corresponding to that item is indicated as main item number + 0.01

Flag “tot” is for Amount field which sums the value by looking up from “h” up to the “tot”

The issue which I am facing is that, for the first item under the next heading, the Sl. No. does not restart based on the flag. It numbers continuously from the last number under the heading above. In the example shown, D21 cell shall indicate 1.00 instead of 4.00.

The formula should return the 1.00 in the Sl. No. field if “h” is encountered i.e. I wish to ignore the any blank row(s) above a particular row (row without any flag). The Sl. No. shall restart for items under the subsequent new headings also. Please assist in correcting the code.

The working file is attached here https://ufile.io/vimtj

Note:
The formula in Cell D21 is

  =IF(B21="","",IF(B21="m",COUNTIF($B$2:B21,"m"),LOOKUP(10^35,$D$1:D19)+0.01))

 

[stunnrock]

If I'm reading all of your requirements correctly, then you want something like this:

=IF($B21="","",IF($B21="h",1,IF($B21="m",COUNTIF(INDEX($B$2:$B21,MAX(INDEX(ROW($B$2:$B21)*($B$2:$B21="h"),))-ROW($B$2:$B21)+1):$B21,"m")+1,IF($B21="s",INDEX($D$2:$D20,MAX(INDEX(ROW($B$2:$B20)*($B$2:$B20<>""),))-ROW($B$2:$B20)+1)+0.01,IF($B21="tot","get total","unknown")))))

 

[melvinkoshy]

I copied this formula in B21 but to no avail

My concern is only about the Sl. No. field. It does not restart on the instance of encountering “h” in the flag field which is for heading.
Under each heading, the numbering should restart. With the formula

=IF(B21="","",IF(B21="m",COUNTIF($B$2:B21,"m"),LOOKUP(10^35,$D$1:D19)+0.01)) ,the numbering does no restart

 

[RoryA]

Please take a minute to read the forum rules, especially regarding cross-posting, and then add links here to your threads in other forums. Thanks.

 

[melvinkoshy]

I apologise for the cross posting. I was not aware of the regulations. I am grateful to the solution provided by @JeteMc in the following link where I cross-posted.

https://www.excelforum.com/excel-ge...t-of-numbering-based-on-code.html#post4864049

This formula has enabled me to get the desired solution. I am posting it for the benefit of the community. Once again, I am sorry for the infraction of cross-posting.