Hi,
I need to perform a count of distinct values in a column and display both the count as well as the distinct value that it is counting.
e.g. if I had 5 entries in A1-> A5 of AA, BB, CC, BB, AA
I would want the results to show
AA - 2
BB - 2
CC - 1
How do I do this?
One way would be to use an Advanced Filter to extract a list of unique entries and then use COUNTIF.
Alternatively, and probably quicker, you could use a Pivot Table.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Hi,
Here's one way of doing this..
Refer workbook attached. You need to use this as an array formula (confirmed by pressing Ctrl + Alt + Enter after inputting the formula).
If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.
If your requirement has been solved please mark your thread as Solved.
In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".
Kindly use [FORMULA] or [CODE] tags when posting your code.
Regards,
Sarang
You mean Ctrl + Shift + Enter. Right?Originally Posted by Saarang84
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Yes sktneer, you're right, Its my mistake..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks