|First, a couple of posting tips:|
1 - It helps us help you if you include as much detail in your original question as possible. If you had included your example data in your first post, I would have been able to supply a solution that fit the actual situation instead of spending time on the wrong problem.
2 - Before posting example data in this forum, please click on the blue line at the bottom of this post and read the instructions given there. Those instructions will show you how to post data so that the columns line up, making it easier for us to read.
OK, assuming columns headings in Row 1 as shown in my example, try this in B2 and drag it down:
To start, if the number of items in your list will change, you should use a Named Range. For example, select Column A and name it myList. That way you won't have to change the formula if you add more names to the bottom.
That's what I did for the formula that follows.
FIND("%",A2&"%"&COUNTIF($A$2:A2,A2)))&" of "&COUNTIF(myList,A2)
How it works:
will append a % and count of each occurrence of a name to the end of each name. e.g. if you used it in a column by itself you'd get this:
will strip off the number from the end of that string.
The % (or some other character) is needed to give Excel something to find in order to determine where your text ends and the number begin. You can use any character you want as long as it won't appear in your list of names.
Finally, that stripped off number is concatenated with "of" and the total count of each item myList.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.