Count Unique records based on the Criteria in another colum
Try something like this:
With your posted list in A1:B10
Then
D1: Any
Put this ARRAY FORMULA* in
E1:
=COUNT(1/FREQUENCY(IF($A$1:$A$10=D1,IF($B$1:$B$10<"",MATCH ($B$1:$B$10,$B$1:$B$10,0))),ROW($B$1:$B$10)-ROW($B$1)+1))
That formula returns the count of unique Col_B items where their
corresponding Col_A value matches the value in D1.
*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
"Rajat" wrote:
I want to count the unique entry (Text & Numeric) based on the condition on
another corresponding row data, following is an example of data set
any Rajat
why Two
who Three
how 5
when Rajat
any 4
why Two
any Rajat
any 5
when Rajat
i want to count perticular value in colum A has how many unique entry in
Colum B.
e.g. "any" has how many unique entry in Colum B
Answer is 3 (any has value of Rajat, 4, Rajat, 5)
In this case what formula should i use, any suggestion will be of great help
to me.
|