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