Yearly repeated and new clients

broiling

New member
Joined
Nov 30, 2016
Messages
5
Reaction score
0
Points
0
Hello,

I am trying to identify the number of new and repeated customers yearly.

536787d1504687862-yearly-new-and-repeated-customers-xhcf2.png


According to above table, I listed down the new and repeated customers but this process has been done by manually and I do have a lot of customers to identify in this way.

536788d1504687888-yearly-new-and-repeated-customers-untitled.png


May I know is there any formula, so I can identify the number of new/repeated customers yearly.

Your help much appreciated.

Thanks
 
Last edited:

Pecoflyer

Admin Alumnus
Joined
Oct 13, 2011
Messages
1,770
Reaction score
0
Points
36
Location
Brussels Belgium
Excel Version(s)
2010 on Xubuntu - O365
Please post a sample sheet ( click "go advanced" - Manage attachments)
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Try conditional formatting with a formula of

=AND(B3=1,COUNTIF($A3:B3,1)=1)
 

broiling

New member
Joined
Nov 30, 2016
Messages
5
Reaction score
0
Points
0
Try conditional formatting with a formula of

=AND(B3=1,COUNTIF($A3:B3,1)=1)

Hi Bob,

I understand your approach but however, I will not be working because of the figures. 1 number represents the fees which mean figures will be quite different than each other and some would be - (minus) figures too

thanks
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Okay, try this one

=AND(B3<>"",COUNTA($B3:B3)=1)
 

broiling

New member
Joined
Nov 30, 2016
Messages
5
Reaction score
0
Points
0
Okay, try this one

=AND(B3<>"",COUNTA($B3:B3)=1)

I think there is a miss understanding. Actually, the reference point is the previous years of the current year. So if we are on 2016, we have to reference the range of 2012-2016, not 2012. I don't have data before 2012 so, I just took the number of customers after 2012.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
You asked for << I am trying to identify the number of new and repeated customers yearly. >>, so my approach shows you the year it became new, any other year it is repeated. f you look down column 2016 you can see all those new for the year, all those repeated. Seems what you asked for to me.
 
Top