Please note that this is an array formula. press CTRL+SHIFT+ENTER to enter
the formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula}"

=SUM(IF(FREQUENCY(MATCH(TRIM(B2:B5),TRIM(B2:B5),0) ,
MATCH(TRIM(B2:B5),TRIM(B2:B5),0))0,1))

If this post helps click Yes
---------------
Jacob Skaria


"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