Hello All,

I am trying to create a sales tracker that will summarise to create a pipeline forecast based on a specified date range. I need it to report on 4 products separately based on a hierarchy of variables. I am not sure if it is possible to do this or it would be easier to use a Macro code (I don't know how to this!).

In the attached spreadsheet I have created a second tab that I have begun to try to write a formula that does the following;

Start Date: Entered by the user
End Date: Automatically adds 6 days on the start date creating the desired date range

Interest: This would identify any customer that has an interest date within the specified date range for the specific product
New Demo: This would identify any customer that has a demo date in the specified date range for a specific product but has not had a demo date previously (I have used the unique customer ID to count this)
Mid Trial (On Trial Now): This would identify any customer that is on trial during the specified date range for a specific product
End Trial Yet to Subscribe: This would identify any client that has an expired trial but no invoice within the specified date range for the specific product
Invoices: This would identify any customer that has been invoiced within the specified date range for a specific product

As this is a pipeline forecast I want the summary to only report each client once so the heirarchy would be the above in reverse order (Invoice, End Trial, Mid Trial, New Demo, Interest)

I am aware that this is a HUGE ask but if anyone could save me, I would be eternally in your debt! If it is not possible I would be grateful if you could let me know too so that I can look at other options.

Thank youTest Tracker.xlsx