Hi everyone. I am looking for a way to insert serial numbers in rows which have irregular gaps between them. For example, the 2nd row that contains information is located 3 rows after the first row, third row that contains information is located 5 rows after the second and so on.
Regards
Do you mean something like this:
Data Range
This formula entered in A2 and copied down:
=IF(B2="","",COUNTA(B$2:B2))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
yes, that is what I meant, but somehow that formula does not appear to be working. I am sure I am making a mistake with it
Last edited by terry3218; 10-12-2015 at 01:58 PM.
yes, that is what I meant, but somehow that formula does not appear to be working.Capture.PNG I am sure I am making a mistake with it
Post a file rather than an image.
Can't see *.png images.
You might have to replace the commas with semi-colons:
=IF(B2="";"";COUNTA(B$2:B2))
@Tony, I am afraid this formula is giving an error. I am trying but I cant insert an image here. Still thanks for your replies anyway.
Try loading a sample file:
Click "Go Advanced" then the Paper Clip Icon.
"Click Add Files", then "Browse", select your file, "Upload" then Done"
Thanks a lot. I have done that. Now in this file I have manually inserted the serial no.s but I want a formula to do that.
I inserted this into A2 (as per Tony's reply) and copied down and it produced the correct result
=IF(B2="","",COUNTA(B$2:B2))
Thanks a billion.Originally Posted by JohnTopley
Can you also share what is the significance of quotation marks(inside of quotation mark) here?
B2="" checks if the cell is "empty" (null): if so A2 is set to null, otherwise it is set to the serial number calculated by the COUNTA function
=IF(B2=null, null (true result), COUNTA(b$2:b2) (false result))
thanks a lot. I really appreciate.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks