CountIF formula not working for Churn reporting

Hi,

I have a table of customer transactions by month and I am wanting to find out the number of new and lost customers each month. I have attached a sample of data on a very small scale of what I am currently working on. I am using countIF formulas to work out the number of lost customers each month, however this is not working. The number of lost users is incorrect because the formula doesn’t take into account when gaps exist in the customer usage. For example, if a customer signs up in Feb 2018, but not in March – this should be a lost user in March. However, if this same customer signs up again in Apr 2018 and continues using the service until December 2018, this will be a lost user in January 2019. There are many gaps in customer usage, so I need to work out a different way of doing this. Refer to cell F32. This cell should equal 2 however it is only one. Users Harry and Amy did not resign plans in Feb 2018 therefore two users have been lost. However, Amy resigned in April 2018 and her last plan date is Nov 2018.

Any suggestions would be greatly appreciated!

Thank you!

OK Look at the file specilly at the plan section. I populated each empty cell with a formula that simply asks if there has been a sign up before, if so place a 0 if not leave it empty.
The in the LOST CUSTOMERS line I simply count the 0 (different from the empty) and multiply it by -60.