counting unique items(values or text)

[guneet_ahuja]

hi,

I want to count unique items in a list with an array formula, like th
items is in 3 columns A,B,C are from row 2 to 101. Now i use an arra
to filter out some rows in the columns of B,C & then count unique item
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 uniqu
values in column A.

pls reply as soon as possible
thanks & regard

 

[Bob Phillips]

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]...

 

[guneet_ahuja]

hi,

ur formula is nice but how do i make it work along with othe
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 wher
corresponding value in column B is >0 & value in column C <>1.

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

 

[Bob Phillips]

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]...

 

[guneet_ahuja]

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

 

[Bob Phillips]

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]...

 

[guneet_ahuja]

this is not working too
I AM GETTING THE ANSWER

 

[Domenic]

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!

guneet_ahuja

 

[guneet_ahuja]

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 ahuj

 

[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

 

[Domenic]

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:

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!

guneet_ahuja

 

[guneet_ahuja]

hi,

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

cya
t