Two Numbering Techniques For Counting And Labeling Lists In Excel

In this tutorial we are going to look at two methods of labeling or numbering accounts, locations, inventory, etc. using IF statements and Custom Formatting to count the frequency in a list or label the items in a sequence.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

I have a list that could represent parts, inventory, accounts, customers, etc.:

1716-1

I want to number this list two ways:

1) Count the number of instances of the same name in the list;

2) Number each unique instance of a name, then increase the next name by one.

Count the number of instances of the same name in the list:

To accomplish this, all I need to do is enter the following formula in the cell next to the first item in the list, in this case cell C2:

=IF(D2=D1,C1+1,1)

Then copy it down to the bottom of my list:

1716-2

This formula will check to see if the name above is the same. If so, it will take the number above and add 1 to it, if not it will insert the number 1.

If I then want to convert these to values only, I can use this trick:

1716-3

I right-click on the edge of the highlighted area, drag it off, then back on the same range, and click on “Copy Here as Values Only”.

Number each unique instance of a name, then increase the next name by one:

In this case I want to number the same items with the same number, then when the item changes, increase the number by one. To start, I need to enter into the first cell whatever starting number I want to use. Here I will enter “201”:

1716-4

Then, in the cell below, I will enter this formula and copy it down:

=IF(H3=H2,G2,G2+1)

1716-5

This formula will check to see if the name above is the same. If it is, it will enter the same number as above. If not, it will add a 1 to it.

Now, let’s say that I want to add a location indicator to the beginning of the number, for example, “GR-” so that the numbers will be GR-201, GR-202, etc. To do this, I’ll create some custom formatting. I’ll highlight the cells I want to format, call up the Format Cells dialog box (I use the keyboard shortcut Ctrl + 1), click on Custom and enter into the Type box “GR-“000:

1715-6

This tell Excel to put the label GR- in front of whatever number I enter, and make that number 3 digits long:

1716-7

So, notice that, even though I only entered 201 in cell G2, the formatting changed it to GR-201, and formatted all the cells below in the same manner.

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , , ,

No comments yet.

Leave a Reply