List of Lost Cutomers

[RIZVI]

Hi,

I am using Excel 2007.

I want to populate list of customers whom I have lost and are no longer buying from me. I have a huge table with more than 760 customers in listed in Column C from C7 to C2500.
In Column H, I have date on which they had purchased. Most of them are repeat customers.
In Column I, amount of purchase is mentioned.

This tables contains records from year 2015 till date.

Over period some costumers have stopped buying from me and some new have added.

In Separate columns I want:

1. List of customers whom I hv lost in current financial year, 2018-19; but were buying from me in previous years.
2. Likewise in adjacent column I want a list of customers whom I lost last year, that is in 2017-18, but were buying from me earlier.
3. Likewsie same for customers I lost in 2016-17.

Customers who joined me in subsequent years must not come in the list of lost customers. Say, if a customer joined me in year 2017-18, he must not show as lost customer in year 2016-17.


Is there some formula by which I can obtain the same?


Thanking in anticipation of help,

With regards,

Rizvi.M.H.

 

[MUKESHY12390]

Here is idea which you can apply.it can be done several ways .

since you want list of lost customer list, first you need to segregate between "Active customer vs Lost customer" for all year 2016,2017,2018 and 2019.

Add Four column to your data (give heading Cust_2016,Cust_2017,Cust_2018 and Cust_2019)

Enter this formula "

=IF(SUMIFS(I:I,C:C,">=1/1/2018",C:C,"<=12/31/2018")>0,"Active Customer","No Order this year")

I column is for [COLOR=#333333]amount of purchase
[/COLOR]C column is for purchase date

Do this for year 2016,2017 and for 2019(in case any future order).

after that you will have list customer for each year then used your criteria for Lost customer.