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

 

[s0nicstang]

try

=SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000&""))

 

[brooklyn2007]

try

=SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000&""))

Nope, it gives me a 9 result instead of 3

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		9
6	John
7	John
8	Ben
9	George
10	Jim
11	Ken
12	Ben
13	John
14	Tim
15	Mike
16
Sheet1

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

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

 

[s0nicstang]

Opps miss understood what you were looking for

 

[brooklyn2007]

Control+shift+enter, not just enter:

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

Again, it is not working. This time is giving me 8

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		8
6	John
7	John
8	Ben
9	George
10	Jim
11	Ken
12	Ben
13	John
14	Tim
15	Mike
16
Sheet1

 

[brooklyn2007]

Allow me to explain better. The result which I'm looking should be 3. John, Jim, Ben. Even thought these three names are repeated different times in the range, each of them should count as one.

 

[Marcelo Branco]

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.

 

[brooklyn2007]

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

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		8
6	John
7	John
8	Ben
9	George
10	Jim
11	Ken
12	Ben
13	John
14	Tim
15	Mike
16
Sheet1

 

[Aladin Akyurek]

Allow me to explain better. The result which I'm looking should be 3. John, Jim, Ben. Even thought these three names are repeated different times in the range, each of them should count as one.

Control+shift+enter, not just enter:

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

Forget that with TRIM's ...