Calculate Av of col B using unique values in col A

[towners]

Hi,

I have customer numbers in column A with the number of visits in column B.

There are duplicates in the customer number column (one record per visit), I calculated column B to count the number of visits for each customer so I will have the calculated number of visits also duplicated for each duplicate customer.

i.e.

Customer 1 - number visits 1
Customer 2 - number visits 2
Customer 2 - number visits 2
Customer 3 - number visits 3
Customer 3 - number visits 3
Customer 3 - number visits 3
Customer 4 - number visits 1


I want to calculate, using a formula the average number of visits for unique instances of customer.
And, the percentage of customers visited more than once.

So my calculation would be (average column B for column A unique values only)

Any help would be appreciated.

Thanks

Towners

 

[DHayes]

Hope this is what you need.
[TABLE="width: 303"]
<tbody>[TR]
[TD]Customer 1
[/TD]
[TD]-
[/TD]
[TD]number visits
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Customer 2
[/TD]
[TD]-
[/TD]
[TD]number visits
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Customer 2
[/TD]
[TD]-
[/TD]
[TD]number visits
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Customer 3
[/TD]
[TD]-
[/TD]
[TD]number visits
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Customer 3
[/TD]
[TD]-
[/TD]
[TD]number visits
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Customer 3
[/TD]
[TD]-
[/TD]
[TD]number visits
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Customer 4
[/TD]
[TD]-
[/TD]
[TD]number visits
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 2
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 3
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 4
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Formula

=IFERROR(AVERAGEIFS($D$1:$D$7,$A$1:$A$7,A10,$D$1:$D$7,">1"),1)

 

[towners]

Hi,

Thanks for the formula. I'm not getting the result I expected though.

I use this formula to count the number of unique values in my column A (it's a named range called "D_CustomerID") with a couple of criteria.

{=SUM(--(FREQUENCY(IF((D_FirstDeliveryYearMonth=AY$2)*(D_PT_Brand<>"NONE"),MATCH(D_CustomerID,D_CustomerID,0)),ROW(D_CustomerID)-ROW(D_FirstCustomerID)+1)>0))}

I was wondering how I would adapt this (now I know the number of unique occurrences) to produce the average of Column B