first, thanks in advance for any help. I searched through as many threads to see if this was already answered, but no luck.
I have a list of values in column A :
apple
plum
orange
melon
apple
apple
orange
banana
In other sheet there is other list ( column B) :
plum
apple
strawberry
peach
I want to know how many unique values are in column A that are present in column B:
So the result of formula should be : 2
( apple, plum )
Try this formula
in B2 and copy it down
=LOOKUP("zzzz",CHOOSE({1,2},"",INDEX($A$2:$A$9,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$9),,),))))
apple apple plum plum orange orange melon melon apple banana apple orange banana
or if you just want to count unique values in column A use formula below
=SUMPRODUCT((A2:A30<>"")/COUNTIF(A2:A30,A2:A30&""))
Last edited by AlKey; 07-24-2014 at 05:28 PM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
AlKey, thank you for replay, but I'm searching for something different.
I want to count unique values in column A, but only those which are also in column B. The result of formula should be : 2
I'm combining different functions ( SUMIF, COUNIFS, MATCH, INDEX, SUMPRODUCT, FREQUENCY ) for two days with no luck.
Link to XLSX file :
https://drive.google.com/file/d/0Bws...it?usp=sharing
Try this then
=SUMPRODUCT((A1:B30<>"")/COUNTIF(A1:B30,A1:B30&""))-SUMPRODUCT((A1:A30<>"")/COUNTIF(A1:A30,A1:A30&""))
Thank AlKey, I was happy for a moment - the result was good, but then I added some values to column B and the your formula was not working with additional data.
Try this...
Data Range
This array formula** entered in D1:
=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(A1:A8,B1:B8,0)),MATCH(A1:A8,A1:A8,0)),ROW(A1:A8)-ROW(A1)+1),1))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
WOOOOOOWthank you Tony , this is exactly what I was looking for !!!
You are my hero.
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks