Find Sales by agent in First 15 days then next 15 days

Houstonking

New Member
Joined
Jul 12, 2016
Messages
30
Hello,

I am confused as hell how to figure out when the agent had first sale and since first sale what did he sell in next 15 days. then again in next 15 days. I have the data by each day of the year. There are over 100 sales people. what i need to fig out what formula to put so that it takes the sales person hired in feb 3 or march 4 or april 25 having a sales on his first day then what he did in next 15 days and sum it up. each agent started on separate days and i want the 15 days to start from their first sale data. Any ideas?? please help. Thanks
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
You might need to post an example of your data. All of the formulas are possible, but will depend what the data looks like - you need to be clearer in your question, which is why it's taken so long for anyone to respond. Is it one block with names and dates in 2 long columns?

Assuming names in column A, dates in column B and sales values in column C; with a separate table of names starting in E2 downwards:

F2: first sale date =min(if(A:A=F2,B:B)) entered as an array formula, i.e. using Shift + Ctrl + Enter instead of just Enter
G2: end of 16 day period = F2 +15
H2: sales in period =SUMIFS(C:C,A:A,E2,B:B,">="&F2,B:B,"<="&G2)
 

sergioMabres

Well-known Member
Joined
Feb 24, 2013
Messages
946
Hi Houston
One way to do this, there are many ways to do this, with this data set

VijjlkTe7Yfc7BDFpTJOVCZiJFbr75jwxnAzNewZIuQ


You create an auxiliary column with the formula in D2 {=IF(B2-MIN(IF($A$2:$A$30=A2,$B$2:$B$30))>15,0,C2)} this is a matrix formula so you should end with shift+control+enter

Then create a table at the side to sum up first 15 day of ever sales person using the formula in G2 =SUMIF(A2:$A$30,F2,$D$2:$D$30)

You can access a test file here https://onedrive.live.com/view.aspx?resid=52B7AEC35713C28B!2013&app=Excel
Cheers
Sergio
 

Houstonking

New Member
Joined
Jul 12, 2016
Messages
30
sales person1/1/20161/2/20161/3/20161/4/20161/5/20161/6/20161/7/20161/8/20161/9/20161/10/20161/11/20161/12/20161/13/2016################################################################################################################################################2/1/20162/2/20162/3/20162/4/20162/5/20162/6/20162/7/20162/8/20162/9/20162/10/2016Total sales
joe3333111116
karen22234417
pete111144444428

<colgroup><col><col span="9"><col span="4"><col span="27"><col><col></colgroup><tbody>
</tbody>
 

Houstonking

New Member
Joined
Jul 12, 2016
Messages
30

ADVERTISEMENT

above is the data - if you see joe first sale date is 1/5 and karen is 1/3. so how can i sum their sales starting from first sale to 15 days then sum next 15 days?
 

sergioMabres

Well-known Member
Joined
Feb 24, 2013
Messages
946
Hi Houston,
Your data is ill oriented, but anyways:

You create an auxiliary column, let us say MIN+15, to the right of total, with the formula in AR2 {=MIN(IF(B2:AP2>0,$B$1:$AP$1))+15} this is a matrix formula so you should end with shift+control+enter

Then create another column, let us say Frst 15 D, to the right of MIN+15, using the formula in AS2 =SUMIF($B$1:$AP$1,"<"&AR2,B2:AP2) this one is not matrix so end with enter

You can access a test file here https://1drv.ms/x/s!AovCE1fDrrdSj13cplhAw_1nyJ1r
Cheers
Sergio
 

Houstonking

New Member
Joined
Jul 12, 2016
Messages
30

ADVERTISEMENT

Great thanks alot!!!
 

sergioMabres

Well-known Member
Joined
Feb 24, 2013
Messages
946
I think Houston wants to ask now how can I get the next 15 days and the next and so forth that is sum if between 15 and 30, then 31 to 45 that is what he will be asking in the new thread, the original thread is how to discover the 15 days after the first sale
Cheers
Sergio
 

Watch MrExcel Video

Forum statistics

Threads
1,133,538
Messages
5,659,384
Members
418,500
Latest member
Guru Prasad S

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top