EXCEL: How to create running number for data with loopholes

Hello! change of content!
Today Im going to show you how to automatically create a series of running number/tracking number in excel. :)

(A)Usage:
1. create running number when only you type them list of items instead of pre-entering the number first then item.
2. if there is a hole between 2 data, it will still create running number for the cells that only have data and ignoring blank cell.

Let say you want to go for a shopping. and here are the items:












(B)Situation:
You can do this by either:-
1. keying in 1 in cell A2 and drag all the way until A6 or;
2. alternatively you do this formula in A2 and drag it all the way to A6:
=IF(ISBLANK(B2),"",COUNTA($B$2:B2))

Ultimately you will get the same result using either way which is this:









But what is suddenly you decided that you do not want the meat coz you already have chicken, so you want to delete the meat.

1st alternative result
By using the 1st alternative when you delete the meat it will show something like this which leave number 3 undeleted and showing you have 5 items to buy which is wrong.











2nd alternative result
By using the 2nd alternative, once you delete the meat the watermelon will now become the number 3 and it will show you 4 items to buy which is correct!











(C)Application:
Well this does not seem helpful if you are maintaining 5 data but it is helpful if you are talking about thousands of data with any of it can be deleted at any time or data with few blank cells somewhere in the middle(data with loopholes)

(D)The logic:
=IF(ISBLANK(B2),"",COUNTA($B$2:B2))

ISBLANK(B2) means to check whether cell b2 is an empty cell or not. it will return 'TRUE' if it is empty and 'FALSE' if it is not empty.

"" This is to return cell A2 to blank if it is true that cell B2 is blank.

COUNTA($B$2:B2) This is to count all the data from B2 to X where X=until when you drag your formula to. In this case you drag your formula to A6 and the formula will show =IF(ISBLANK(B6),"",COUNTA($B$2:B6))

Anyhow, happy trying. =) do ask if you have question.