Show totals for each cell in a single column

Hi All,

I would like to obtain the total quantity for how often a particular entry appears in a cell. I'm sure that I'm not searching with the proper terminology, but I've been unable to find any examples remotely similar to what I'm looking for.

For instance, my column will have entries such as the following:

B5A
B5
B4
UX4
B7G
UX4
B7G
UX4
UX4
UX4
AO8
UX6
AO8
B4
AO8
AO8
B8B
AO8
AO8


I would like something that tells me the following:

B5A = 1
B5 = 1
B4 = 2
UX4 = 5
B7G = 2
AO8 = 6
UX6 = 1
B8B = 1

Could anyone help to tell me if something like this is even possible?

Thanks!

Andy

Hello,

Thanks for the fast reply and providing the other thread as a reference. I'm confused as to how it would apply in my situation though. It seems that it's comparing information shown in two different columns to provide a result. In my case, all the data I wish to count is in one column only.

Also, for the COUNTIF examples provided at the Microsoft site, it appears that those examples are pre-populated with all the possible values. This would be rather convoluted when there could be as many as 200~250 different values. In addition, I'm not looking for a total number of all the different values, but a total number of how often a particular value appears. I've been looking on this site as well as Google, but not finding exactly what I've been hoping to find to help solve my particular issue. I apologize as most likely I'm not describing it as well as I should be - I'm not familiar enough with Excel or all the associated terms.

Thanks again,

Andy

Make it a table and use a pivot table.

Originally Posted by Vraag en antwoord

Make it a table and use a pivot table.

Thank you. Your result is indeed what I'm looking for, but how did you get there?

I clicked on the first cell of my column, then clicked on the "Insert" menu, then selected "Pivot Table". Some of the data was copied to a new worksheet and listed under Row Labels such as your example. But I have almost 34000 rows of data with roughly 110~120 unique values and only 3 rows appeared under Row Labels in the new worksheet. How do I get all of them to appear? How did you get all the values to appear in your example?

Formula:

Please Login or Register  to view this content.

Originally Posted by GeoffW283

Add the following formula to cell D2 then copy that formula down to D32.

Formula:

Please Login or Register  to view this content.

Column-D now contains the answer that I think you are looking for.

Thanks GeofW283! This works perfectly! I was able to modify the cell references in your formula and receive precisely the results I was looking for.

Andy