Hello all,
Just need a little help with a formula to count the number of unique values within a list.
For example, I have this list:
Text1
Text2
Text3
Text4
Text4
Text5
The formula would need to return the result of 5. "Text4" would only be counted once since it's a duplicate.
Any help is most appreciated - thanks!
Question also posed at Mr Excel: http://www.mrexcel.com/forum/showthr...99#post2716199
Try...
=SUM(1/COUNTIF(A1:A6,A1:A6))
This is a CSE formula which requires Ctrl + Shift + Enter
HTH
Regards, Jeff
Somebody here would prob give you an array formula, but so that you could understand what can be done with simple formulas assuming that your values are on col A starting from A1, then
On B2, copied down as required
=COUNTIF(A$1:A1,A1)
On B2
=SUMIF(B1:B10,1)
Ron
Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad
Kindly
[1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
[2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
[3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks