Series Formula is correct but output is incorrect-CodePudding

I used one formula for series, in which whenever there is any value in a particular cell then the adjacent cell will show the serial number, if empty then the serial number column will also be empty. The Formula I used is:"=IF(B3:B="","",ROW()-2)" ( "-2" because there are 2 rows which have headings) Then I used "=Match143^143,B3:B)" to get the last cell value.

I used the above 2 formulas for 2 sheets, it worked fine for a few days but now as the datasets increased it is showing a total count -2. Suppose the total count is 100 but is showing 98.

I used this formula ( "=IF(B3:B="","",ROW())" ) as well to check but it is also showing a difference of 2.

Thank you in advance

CodePudding user response：

try:

=INDEX(IF(B3:B="",,COUNTIFS(B3:B, "<>", ROW(B3:B), "<="&ROW(B3:B))))

to make it faster use:

=INDEX(LAMBDA(x, IF(x="",,COUNTIFS(x, "<>", ROW(x), "<="&ROW(x))))
 (B3:INDEX(B:B, MAX(ROW(B:B)*(B:B<>"")))))

or:

=INDEX(LAMBDA(z, IF(z="",,SCAN(, z<>"", LAMBDA(x, y, x y))))
 (B3:INDEX(B:B, MAX(ROW(B:B)*(B:B<>"")))))