how to find out how many clients had 2 or more visits.

J

Jman

How can i find out how many clients had more than 1 visit..

I want to creat a formula that counts for me "how many clients have 2 or
more visits.. If possible with the pivot table..
 
J

Jman

Jman said:
How can i find out how many clients had more than 1 visit..

I want to creat a formula that counts for me "how many clients have 1 or
more visits..
Click to expand...


for example

in columnt

A1 B1

John Mcarther Count as 1
John Mcarther
John Mcarther


Julian Boss Count as 0 (since it has no duplicate)


Chris Mccc Count as 1
Chris Mccc
Chris Mccc
Chris Mccc

B19 EQUALS TOTAL OF 2 DUPLICATES
 
T

Teethless mama

Assuming your data in A1:A10 with a header in row 1
Select A1:A10 >> Data >> Filter >> Advanced Filter >> select Copy to another
location >> List range: $A$1:$A$10 >> Copy to: select $B$1 >> select Unique
records only >> OK

In C2: =(COUNTIF($A$2:$A$10,B2)>1)+0
Copy down as far as you want
 
T

T. Valko

Try this:

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))-SUMPRODUCT(--(COUNTIF(A1:A10,A1:A10)=1))

Biff
 
J

Jman

T. Valko said:
Try this:

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))-SUMPRODUCT(--(COUNTIF(A1:A10,A1:A10)=1))

Biff
Click to expand...


Thanks Much!!! this formula worked like a charm..
Julian
 
Top