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

[Houstonking]

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

 

[baitmaster]

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]

Hi Houston
One way to do this, there are many ways to do this, with this data set

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]

sales person

1/1/2016

1/2/2016

1/3/2016

1/4/2016

1/5/2016

1/6/2016

1/7/2016

1/8/2016

1/9/2016

1/10/2016

1/11/2016

1/12/2016

1/13/2016

########

########

########

########

########

########

########

########

########

########

########

########

########

########

########

########

########

########

2/1/2016

2/2/2016

2/3/2016

2/4/2016

2/5/2016

2/6/2016

2/7/2016

2/8/2016

2/9/2016

2/10/2016

Total sales

joe

3

3

3

3

1

1

1

1

16

karen

2

2

2

3

4

4

17

pete

1

1

1

1

4

4

4

4

4

4

28

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

 

[Houstonking]

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]

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]

Great thanks alot!!!

 

[sergioMabres]

Always glad to help
Sergio

 

[FDibbins]

Seems like a duplicate thread here?
http://www.mrexcel.com/forum/excel-questions/979289-15-days-sum-if-criteria.html

 

[sergioMabres]

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