Counting formula

[brooklyn2007]

i have a long column of unique customers who many of them repeat themselves more than once. I need to find a formula to calculate the total number of these unique customers who have bought more than once. It does not matter if some of them bought two, three or infinite times. It has to calculate it as one repeat. Below is a example of the column and the result that should come, the formula is missing. Thanks

Excel Workbook
	A	B	C	D	E	F	G	H
1	Find the total number of unique names that repeat more than once
2
3	John		Answer	3
4	Jim
5	Tom		Formula to use		??????
6	John
7	John
8	Ben
9	George
10	Jim
11	Ken
12	Ben
13	John
14	Tim
15	Mike
16
17
Sheet1

 

[brooklyn2007]

Try this

=SUM(IF(FREQUENCY(IF(A3:A100<>"",MATCH("~"&A3:A100,A3:A100&"",0)),ROW(A3:A100)-ROW(A3)+1)>1,1))

Ctrl+Shift+Enter

M.

Yes Marchelo, this one is giving me the result I wanted. Thanks a lot

Excel Workbook
	A	B	C	D	E	F	G
1	Find the total number of unique names that repeat more than once
2	*	*	*	*	*	*	*
3	John	*	Answer	3	*	*	*
4	Jim	*	*	*	*	*	*
5	Tom	*	Formula to use		3	*	*
6	John	*	*	*	*	*	*
7	John	*	*	*	*	*	*
8	Ben	*	*	*	*	*	*
9	George	*	*	*	*	*	*
10	Jim	*	*	*	*	*	*
11	Ken	*	*	*	*	*	*
12	Ben	*	*	*	*	*	*
13	John	*	*	*	*	*	*
14	Tim	*	*	*	*	*	*
15	Mike	*	*	*	*	*	*
Sheet1

 

[Marcelo Branco]

Again is 8. It should be 3. I'm using CTRL + Shift + Enter

Did you try my formula?

M.

 

[Marcelo Branco]

Yes Marchelo, this one is giving me the result I wanted. Thanks a lot

You are welcome

M.