counting unique items(values or text)

hi,

I want to count unique items in a list with an array formula, like the items is in 3 columns A,B,C are from row 2 to 101. Now i use an array to filter out some rows in the columns of B,C & then count unique items in Column A.

so,
column A has "30 diffrent names repeated from A2 to A101"
column B has numbers 0 to 100
column c has value either 0 or 1

now i use an formula TO filter out rows in column B & c
formula =count(IF((B2:B101>0)*(C2:C101<>1),1))) using ctrl+shift+enter

now what should i suffix or prefix to this formula to count unique values in column A.

pls reply as soon as possible
thanks & regards

Try this

=SUMPRODUCT((SUBTOTAL(3,OFFSET(A2,ROW($A$2:$A$101)-ROW($A$2),,1)))/COUNTIF(A
2:A101,A2:A101&""))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"guneet_ahuja" <[email protected]>
wrote in message
news:[email protected]...
>
> hi,
>
> I want to count unique items in a list with an array formula, like the
> items is in 3 columns A,B,C are from row 2 to 101. Now i use an array
> to filter out some rows in the columns of B,C & then count unique items
> in Column A.
>
> so,
> column A has "30 diffrent names repeated from A2 to A101"
> column B has numbers 0 to 100
> column c has value either 0 or 1
>
> now i use an formula TO filter out rows in column B & c
> formula =count(IF((B2:B101>0)*(C2:C101<>1),1))) using ctrl+shift+enter
>
> now what should i suffix or prefix to this formula to count unique
> values in column A.
>
> pls reply as soon as possible
> thanks & regards
>
>
> --
> guneet_ahuja
> ------------------------------------------------------------------------
> guneet_ahuja's Profile:
http://www.excelforum.com/member.php...o&userid=37061
> View this thread: http://www.excelforum.com/showthread...hreadid=567815
>

hi,

ur formula is nice but how do i make it work along with other conditions i want.

bcoz what I want is, if there are 3 columns
when value is greater than 0 in column B
and value is not equal to 1 in column C
then give me unique count of items in column A

thus it will give me unique count of those cells in column A where corresponding value in column B is >0 & value in column C <>1.

thanks & regards
pls tell me if my conditions are unclear to you.

I thought you said that you were filtering it. Try this instead

=SUMPRODUCT((A2:A101>0)*(B2:B101<>1))/COUNTIF(A2:A101,A2:A101&""))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"guneet_ahuja" <[email protected]>
wrote in message
news:[email protected]...
>
> hi,
>
> ur formula is nice but how do i make it work along with other
> conditions i want.
>
> bcoz what I want is, if there are 3 columns
> when value is greater than 0 in column B
> and value is not equal to 1 in column C
> then give me unique count of items in column A
>
> thus it will give me unique count of those cells in column A where
> corresponding value in column B is >0 & value in column C <>1.
>
> thanks & regards
> pls tell me if my conditions are unclear to you.
>
>
> --
> guneet_ahuja
> ------------------------------------------------------------------------
> guneet_ahuja's Profile:
http://www.excelforum.com/member.php...o&userid=37061
> View this thread: http://www.excelforum.com/showthread...hreadid=567815
>

hi,
i know i troubling you a lot but this
=SUMPRODUCT((A2:A101>0)*(B2:B101<>1))/COUNTIF(A2:A101,A2:A101&""))
i am not getting the right answer also the answer i get is in decimals.

thus still not getting the no. of unique items in column A when values in B>0 & value in column C<>1

No problem, it is my fault, you cannot extend SP like that.

Try this

=SUM(IF(FREQUENCY(IF((A2:A101>0)*(B2:B101<>1),A2:A101),IF((A2:A101>0)*(B2:B1
01<>1),A2:A101))>0,1))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"guneet_ahuja" <[email protected]>
wrote in message
news:[email protected]...
>
> hi,
> i know i troubling you a lot but this
> =SUMPRODUCT((A2:A101>0)*(B2:B101<>1))/COUNTIF(A2:A101,A2:A101&""))
> i am not getting the right answer also the answer i get is in
> decimals.
>
> thus still not getting the no. of unique items in column A when values
> in B>0 & value in column C<>1
>
>
> --
> guneet_ahuja
> ------------------------------------------------------------------------
> guneet_ahuja's Profile:
http://www.excelforum.com/member.php...o&userid=37061
> View this thread: http://www.excelforum.com/showthread...hreadid=567815
>

this is not working too
I AM GETTING THE ANSWER 0

Bob's formula can be amended as follows...

=SUM(IF(FREQUENCY(IF(B2:B101>0,IF(C2:C101<>1,MATCH(A2:A101,A2:A101,0))),R
OW(A2:A101)-ROW(A2)+1)>0,1))

....confirmed with CONTROL+SHIFT+ENTER. Note that if the data can
contain blanks, the formula should be modified.

Hope this helps!

In article <[email protected]>,
guneet_ahuja
<[email protected]> wrote:

> hi,
>
> I want to count unique items in a list with an array formula, like the
> items is in 3 columns A,B,C are from row 2 to 101. Now i use an array
> to filter out some rows in the columns of B,C & then count unique items
> in Column A.
>
> so,
> column A has "30 diffrent names repeated from A2 to A101"
> column B has numbers 0 to 100
> column c has value either 0 or 1
>
> now i use an formula TO filter out rows in column B & c
> formula =count(IF((B2:B101>0)*(C2:C101<>1),1))) using ctrl+shift+enter
>
> now what should i suffix or prefix to this formula to count unique
> values in column A.
>
> pls reply as soon as possible
> thanks & regards

well,
finally the formula worked

u guys are just amasing, u people r seriously the excel champs

u rock man

a big thanks to you BOB

AND THANK U Domenic u did the trick

thanks & regards
guneet ahuja

hi,

well u gave me an excellent formula to count the unique values,

but now please give an formula which will give a unique list i.e with same condition as above i want the unique list of column A & not just the count of these unique values.

thanks & regards

Assuming that A2:C100 contains the data, and that D2 contains the first
formula which returns the number of unique entries, try the following
formula which needs to be confirmed with CONTROL+SHIFT+ENTER...

E2, copied down:

=IF(ROWS(E$2:E2)<=$D$2,INDEX(A$2:A$100,SMALL(IF(B$2:B$100>0,IF(C$2:C$100<
>1,IF(MATCH(A$2:A$100,A$2:A$100,0)=ROW(A$2:A$100)-ROW(A$2)+1,ROW(A$2:A$10
0)-ROW(A$2)+1))),ROWS(E$2:E2))),"")

Hope this helps!

In article <[email protected]>,
guneet_ahuja
<[email protected]> wrote:

> hi,
>
> well u gave me an excellent formula to count the unique values,
>
> but now please give an formula which will give a unique list i.e with
> same condition as above i want the unique list of column A & not just
> the count of these unique values.
>
> thanks & regards

hi,

well it works absolutely perfectly, i had a problem with blanks in the data but fixed it using indirect function, now everything is working gr8.

cya
tc