Need to get number of visits made by sales person till the time of first order placed by the customer

[Pardhu Teja]

I am analyzing the sales data for the FY 23-24 , So i have the data of number of visits made by the sales person monthly and number of orders placed by the same customer monthly.

I need to calculate the number of visits made by the salesperson before the customer placed the first order

(Eg: Sales person visited customer (X) 2,3,5 times respectively for the months of Apr, Mar and May and he made the customer to place order on May. So the result for this is 10)

Is there any formula for the same

Attaching the sample sheet for reference

 

[GraH - Guido]

I believe the raw data should contain the real dates of visits and orders before one can create a measure counting the number of visits between previous order and last order. Because that's basically what you want to do.
Else if the visit is in the same month, it can be either before or even after the order.

 

[Pardhu Teja]

I believe the raw data should contain the real dates of visits and orders before one can create a measure counting the number of visits between previous order and last order. Because that's basically what you want to do.
Else if the visit is in the same month, it can be either before or even after the order.

Hi GraH

I have the data in real dates. I have converted the dates into months as the data looks easier to understand. But, I was unable to calculate the number of visits made before the first order placed.

Any other method to do?? From the raw data real dates directly?

 

[vletm]

Pardhu Teja​

for cell S3 =SUMIFS(C3:I3,C$2:I$2,"<"&INDEX(J$2:P$2,1,MATCH(TRUE,INDEX(J3:P3>0,0),0)))
and copy down

 

[p45cal]

I have converted the dates into months

Let's have a look at the raw data…

 

[Pardhu Teja]

for cell S3 =SUMIFS(C3:I3,C$2:I$2,"<"&INDEX(J$2:P$2,1,MATCH(TRUE,INDEX(J3:P3>0,0),0)))
and copy down

Thanks vletm,

This works. But, I need some more help. How to calculate the sum which includes the order received month visits too (The above formula is calculating visits till the before month of the first order, I need to include the order received month visits too)

 

[Pardhu Teja]

Let's have a look at the raw data…

Hi,

I am attaching the sample Rawdata. please check

This has two sheets. One for visits and one for orders

 

[vletm]

Pardhu Teja​

Your second option can get by changing "<" to "<=" in that formula.
... and You've to take care those 'Month' looking dates Yourself.

 

[p45cal]

attaching the sample Rawdata. please check

See formula in cell G2 of the Orders sheet in Chandoo55444_Formula_Raw data03.xlsx

Separately, a Power Query solution at cell G1 of the Orders sheet of the file Chandoo55444_PQ_Raw data.xlsx

 

[vletm]

ashwaganda​

This thread belongs to Pardhu Teja
You should open a new thread.
Please, reread Forum Rules before You'll open it.