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:B1010)*(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

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"
wrote in message
news:guneet_ahuja.2byku1_1154599206.2397@excelforu m-nospam.com...

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:B1010)*(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.


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

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

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


--
HTH

Bob Phillips

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

"guneet_ahuja"
wrote in message
news:guneet_ahuja.2byq5p_1154606107.3123@excelforu m-nospam.com...

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:A1010)*(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 B0 & 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

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

Try this

=SUM(IF(FREQUENCY(IF((A2:A1010)*(B2:B101<1),A2:A 101),IF((A2:A1010)*(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"
wrote in message
news:guneet_ahuja.2byyhs_1154616913.2466@excelforu m-nospam.com...

hi,
i know i troubling you a lot but this
=SUMPRODUCT((A2:A1010)*(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 B0 & 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


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

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

=SUM(IF(FREQUENCY(IF(B2:B1010,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 ,
guneet_ahuja
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:B1010)*(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


--
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,

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


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

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$1000,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 ,
guneet_ahuja
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


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