Thread: Count the number of occurences of a value

Hi there,

I have been trying to solve a problem but i cant seem to get it to work. Basically I have a list and the list contains the same string multiple times. I am trying to take that list and print it in column D and then list in column E the number of times that string appears in column A. S for example I would like the list to output

Col A | Col B| ....| Col D | Col E |
String1__________String1 3
String1__________String2 1
String1__________String3 2
String2
String3
String3


I though I almost had it but I think I've spent too long looking at it and its become a blurr now!

Thanks for any help anyone can offer me.

Jag


I have attached an example worksheet that I am currently using.

First u need to get unique values of items stroed in columnA

Select All your values in column A then go to DATA > FILTER > ADVANCED FILTER
Select COPY TO ANOTHER LOCATION and check UNIQUE VALUES ONLY.
In the 3rd "Copy to" filed of dialog box select D1 and press OK

After You've got unique values place this formula in E2 and copy down as far as needed

Code:

=COUNTIF($A$2:$A$851,D2)

PS: U have 30 posts. And I think u usually solve your problems on this board. Please specify you xl version,

The above procedure will only work if Column A has a header. This is required for Adv. Filter as otherwise String1 will appear twice.

If you're adding headers and considering Advanced Filter... my advice would be to just use a Pivot Table.