i have about 7000 rows
=SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&""))

Array entered** :

=SUM(IF(FREQUENCY(IF(A1:A7000<"",MATCH(A1:A7000,A 1:A7000,0)),ROW(A1:A7000)-ROW(A1)+1),1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

The array formula is significantly more efficient on a range that size.

AVG calc time on 7000 rows:

SUMPRODUCT = 2.88 secs
FREQUENCY = 0.09 secs


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
=SUMPRODUCT((A1:A5<"")/COUNTIF(A1:A5,A1:A5&""))


"J Walia" wrote:

i have about 7000 rows with 5 column but one column has some duplicates i
would like to count duplicates as ones and single as ones also
A B
1 Nika Well
2 Nika Well
3 Mikaney
4 Lisa Web
5 Lisa Web

i used formula as
=sumif(IF(FREQUENCY(MATCH(B2:B5,B2:B5,0),MATCH(B2: B5,B2:B5,0))0,1))
IT GIVE ME THE WRON aSNWER because there is space after B2 cell how i can
trim it before use the Match help me any one