PDA

View Full Version : Yearly repeated and new clients



broiling
2017-09-12, 07:14 AM
Hello,

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

https://www.excelforum.com/attachments/excel-formulas-and-functions/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.

https://www.excelforum.com/attachments/excel-formulas-and-functions/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

Pecoflyer
2017-09-12, 07:50 AM
Please post a sample sheet ( click "go advanced" - Manage attachments)

broiling
2017-09-12, 08:12 AM
Please post a sample sheet ( click "go advanced" - Manage attachments)

Hello,

you can find the sample in the attachment.

Thank you

Bob Phillips
2017-09-12, 08:23 AM
Try conditional formatting with a formula of

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

broiling
2017-09-12, 08:30 AM
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
2017-09-12, 12:45 PM
Okay, try this one

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

broiling
2017-09-13, 02:23 AM
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
2017-09-13, 08:23 AM
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.