Hi,
I have new customer data:
Sales Rep #, Cust #, Cust Name, Year, Month, Date, Revenue (Table in Range (“A:G”)
I need to get New Customer Revenue and New Customer Count for each Sales Rep per year.
Revenue is an easy part; I am struggling with the new customer count because if sales rep 1 opened a new account “AAA” in February, there could be scenario that sales rep 2 starts selling to the same account “AAA” in March, I am counting the revenue sales rep 2 brings towards new customer revenue, but he (sales rep 2 ) is not supposed to get a new customer count credit (otherwise I will be overstating the number of new customers ), so only sales rep 1 should get a new customer count credit because he was the one who started selling first (Rows 12:13)
There also could be a rare scenario when 2 sales people start selling to a new customer at the same time (rows 14:15), I that case I want to give a new customer count credit to both.
In the file attached- Columns ” M:O” show results of using COUNIFS formula – it will significantly overstate new site counts , I would need to get results in columns “S:U”
Thanks!
Bookmarks