count / frequency of visits with conditions

**maymano** · 08-16-2011, 12:32 PM

Hi,

I have a list of customer numbers in column A. In column B I have a "Y" or a "N"

A B

12345 Y
12345 Y
12345 N
12348 Y
12348 Y
12348 Y
12349 N
12349 N
12350 Y

1) Firstly I just want to count the number of times a customer visits shop 1x, 2x etc.

Number of customers who visit X number of times: (from example above)
1x 1 (this is based on 12350 visiting once)
2x 1 (this is based on ID 12349 visiting twice)
3x 2 (this is based on ID 12345 and 12348 visiting 3 times each)
etc

2) Then if possible, I would also like to count the number of customer visits but with taking into account "Y" indicator only. so only count number of visits per customers with "Y".

Thanks.

**tlafferty** · 08-16-2011, 12:43 PM

Use a pivot table. See attached.

**metzgek** · 08-16-2011, 12:43 PM

1.) Create a column with number of visits (say it's column C) Fill in for each customer and sum. If you want to see how many customers visit the shop once, twice, etc., use the following:

=Countif(C1:C200,1) - Counts customers who visit once
=Countif(C1:C200,2) - Counts customers who visit twice
etc., etc.

2). Countifs(C1:C200,1,B1:B200,"Y") - Counts customers who visit once and have a "Y".
etc., etc.