Hi

Just want to add one more thing. 

If a user paid from Jun-17 to Sep-17 and then missed Oct-17 and then again restart payment from Nov-17 to Dec-17, then his enrollment count as 2 months. 

Regards

S Ali

Hi Suhail Ali, I picked up your case and demo file, but can't figure out what you want. I think you explained it well, but I can't find the green and orange cells where you refer to in your text?

Also I don't understand the numbers in the Sheet1 (the 21, 9, 7, etc.).

And can you explain what is the connection between what you have in A1:U16 and W1:AO16?

In your example on the Results sheet you filled in manually the results for January 2018. How can I see in the data (Sheet1) how you came to that results?

So lot's of questions, but they have to be answered first for my understanding I'm afraid. Otherwise it's too difficult to help, or somebody else here must understand what you mean?

Thanks for a reply,

Frans

Hi Frans

Thank you very much for helping me out. 

Actually, the highlighted data did not save properly for which I put manual results. I have again attached the file. I am really sorry for the inconvenience. I can understand the previous explanation was not very good. I am trying my best to explain it again. 

Definition:

Left Users - Those who did not pay in Reporting Month (Like Jan-18 as an example in the attached sheet). 

New User - Those who paid for the first time in the Reporting month.  

Retained User - Those who paid are paying consistently till Reporting Month. 

Please check User Name 7 (Row 7), from Column Z to AK, it gives you the better idea of Retainership and Leavers.

Excel File Summary:

Data Sheet:

Column A represents the unique User Code, and Column B represents User Name. 

In the data, the numbers 5, 6, 7, 21...... represents the amount collected from Users.  

Result Sheet:

In the Result Sheet where formula to be put, I entered the manual results of Jan. (Details mention below:)

Row 3 - After 1 Month) Those who paid in Dec-17 and did not pay in Jan-18 - (2 Users). 

Row 4 - > 1 and <= 3 Months) Those who paid more than 1 month and upto 3 months. but did not pay in Jan

and so on.............. 

For comfort, How I calculated the desired results, I put the results in Column R.  

One again really thanks Frans you for highlighting the glitches in the file and my explanation. Please do let me know if any other info is needed. 

Thank you very much for helping me out with this report. 

Best Regards

Suhail

Hi Suhail, thanks for the explanation and your new file filled with rich colors 🙂

I think I understand what you want.

But I'm afraid there are to many rules to maintain for making a good working formula or combination of formula's. I started simply with count.if and try to combine more of them, but encountered too many problems. So I'm not finished at all!

Hope somebody else here can also have a look. And what I first want to hear is if someone thinks it's possible what Suhail wants? To automate that with Excel?

So sorry I can't help you now. I'll have another look next days and also will look here if some other people find ways or possible ways to come further.

Frans

Hi Suhail

I agree with Frans that what you require is difficult to do.

You can take a look at my attachment to get some ideas.

No guarantee it will work in all situations as too many manual interventions are needed to maintain the range referenced by the formulas.

I had separated the workings to a few columns (instead of combining them as one) so that it is easier to see and maintain.

Hope others have a better solution for you.

Good luck

Sunny

Hi

Thank you so much. Currently, it is working. Now I am going to apply this to my master file and will let you know the results. 

Once again thank you very much for your wonderful support. 

Best Regards

Suhail Ali