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

#### Houstonking

##### New Member
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### baitmaster

##### Well-known Member
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
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

##### New Member
 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

##### New Member
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
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
Great thanks alot!!!

Sergio

#### sergioMabres

##### Well-known Member
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

Replies
7
Views
300
Replies
16
Views
257
Replies
8
Views
406
Replies
7
Views
361
Replies
7
Views
213

1,171,630
Messages
5,876,545
Members
433,199
Latest member
guerin47

### 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.

### Which adblocker are you using?

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

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