Stop calculating when a date is reached

[Lefty099]

Hi guys. I am trying to find a formula that will sum sales up to a specified date, at which point it stops summing.
eg. I have 4 rows dated 01/02/16, 08/02/16, 15/02/16, 22/02/16 and so on.
If those rows are in column B I want the relevant row in Column C to sum up the figures that are updated daily up until the date specified. eg. $10000 up until the 08/02/16.
If we sell another $4000 on the 09/02/16, I need the row dated 15/08/2016 to be $4000 and the previous line to stay at $10000.
There is a master cell at the top which would read total monthly sales, being $14000 at this point.
Brief.
A1 = Running Total of sales.
A2 = 08/02/16 B2= Sales up to A2
A3 = 15/02/16 B3 = Sales Between 08/02/16 and 15/02/16
A4 = 22/02/16 B4 = Sales Between 15/02/16 and 22/02/16

I hope this makes sense.

Thanks

 

[Lefty099]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Today	26/01/2016						Name	Hours Per Week	Written	Profit	Delivered	Profit2	Written Target	Delivered Target	Sales ID #
2	Start Of Month	1/01/2016						Joe	38	#VALUE!	#VALUE!	#VALUE!	#VALUE!	$71,028	$63,925	5755
3	Days In Month	31						Billy	30	#VALUE!	#VALUE!	#VALUE!	#VALUE!	$56,075	$50,467	5723
4	Month Of Year	1						Bazza	38	#VALUE!	#VALUE!	#VALUE!	#VALUE!	$71,028	$63,925	5641
5	Days Gone In Month	25						John	1	#VALUE!	#VALUE!	#VALUE!	#VALUE!	$1,869	$1,682	5788
6	% Of Month Gone	80.65%
7	Total Hours Allocated	107
8	Store Written Target	200000
9	Store Delivered Target	180000
10
11		7.75	Sales Per Period	Total Profit	Sales per Salesperson					Profit Per Salesperson
12	Weekly Target	$17,757.01			5755	5723	5641	5788		5755	5723	5641	5788
13	Week 1	8/01/2016
14	Week 2	15/01/2016
15	Week 3	23/01/2016
16	Week 4	31/01/2016
17		Total	$0.00	$0.00
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Sheet1

Cell Formulas
Range	Formula
J2	=SUMIF('C:\Users\User\Desktop\[New Targets2.xlsx]Written report'!AD:AD,'C:\Users\User\Desktop\[New Targets2.xlsx]Calculations'!H2,'C:\Users\User\Desktop\[New Targets2.xlsx]Written report'!T:T)
J3	=SUMIF('C:\Users\User\Desktop\[New Targets2.xlsx]Written report'!AD:AD,'C:\Users\User\Desktop\[New Targets2.xlsx]Calculations'!H3,'C:\Users\User\Desktop\[New Targets2.xlsx]Written report'!T:T)
J4	=SUMIF('C:\Users\User\Desktop\[New Targets2.xlsx]Written report'!AD:AD,'C:\Users\User\Desktop\[New Targets2.xlsx]Calculations'!H4,'C:\Users\User\Desktop\[New Targets2.xlsx]Written report'!T:T)
J5	=SUMIF('C:\Users\User\Desktop\[New Targets2.xlsx]Written report'!AD:AD,'C:\Users\User\Desktop\[New Targets2.xlsx]Calculations'!H5,'C:\Users\User\Desktop\[New Targets2.xlsx]Written report'!T:T)
K2	=SUMIF('C:\Users\User\Desktop\[New Targets2.xlsx]Written report'!AD:AD,[@Name],'C:\Users\User\Desktop\[New Targets2.xlsx]Written report'!V:V)
K3	=SUMIF('C:\Users\User\Desktop\[New Targets2.xlsx]Written report'!AD:AD,[@Name],'C:\Users\User\Desktop\[New Targets2.xlsx]Written report'!V:V)
K4	=SUMIF('C:\Users\User\Desktop\[New Targets2.xlsx]Written report'!AD:AD,[@Name],'C:\Users\User\Desktop\[New Targets2.xlsx]Written report'!V:V)
K5	=SUMIF('C:\Users\User\Desktop\[New Targets2.xlsx]Written report'!AD:AD,[@Name],'C:\Users\User\Desktop\[New Targets2.xlsx]Written report'!V:V)
L2	=SUMIF('C:\Users\User\Desktop\[New Targets2.xlsx]Delivered report'!T:T,[@Name],'C:\Users\User\Desktop\[New Targets2.xlsx]Delivered report'!K:K)
L3	=SUMIF('C:\Users\User\Desktop\[New Targets2.xlsx]Delivered report'!T:T,[@Name],'C:\Users\User\Desktop\[New Targets2.xlsx]Delivered report'!K:K)
L4	=SUMIF('C:\Users\User\Desktop\[New Targets2.xlsx]Delivered report'!T:T,[@Name],'C:\Users\User\Desktop\[New Targets2.xlsx]Delivered report'!K:K)
L5	=SUMIF('C:\Users\User\Desktop\[New Targets2.xlsx]Delivered report'!T:T,[@Name],'C:\Users\User\Desktop\[New Targets2.xlsx]Delivered report'!K:K)
M2	=SUMIF('C:\Users\User\Desktop\[New Targets2.xlsx]Delivered report'!T:T,[@Name],'C:\Users\User\Desktop\[New Targets2.xlsx]Delivered report'!L:L)
M3	=SUMIF('C:\Users\User\Desktop\[New Targets2.xlsx]Delivered report'!T:T,[@Name],'C:\Users\User\Desktop\[New Targets2.xlsx]Delivered report'!L:L)
M4	=SUMIF('C:\Users\User\Desktop\[New Targets2.xlsx]Delivered report'!T:T,[@Name],'C:\Users\User\Desktop\[New Targets2.xlsx]Delivered report'!L:L)
M5	=SUMIF('C:\Users\User\Desktop\[New Targets2.xlsx]Delivered report'!T:T,[@Name],'C:\Users\User\Desktop\[New Targets2.xlsx]Delivered report'!L:L)
N2	=($B$8/$B$7)*I2
N3	=($B$8/$B$7)*I3
N4	=($B$8/$B$7)*I4
N5	=($B$8/$B$7)*I5
O2	=($B$9/$B$7)*I2
O3	=($B$9/$B$7)*I3
O4	=($B$9/$B$7)*I4
O5	=($B$9/$B$7)*I5
B1	=TODAY()
B2	=TODAY()-DAY(TODAY())+1
B3	=DAY(DATE(YEAR(B1),MONTH(B1)+1,1)-1)
B4	=MONTH(DATE(YEAR(B1),MONTH(B1)+1,1)-1)
B5	=TODAY()-B2
B6	=B5/B3
B7	=SUM(I:I)
B8	='C:\Users\User\Desktop\[New Targets2.xlsx]Main Page'!J1
B9	='C:\Users\User\Desktop\[New Targets2.xlsx]Main Page'!J2
B11	=B3/4
B12	=(N2/B3)*B11
B13	=B2+7
B14	=B13+B11
B15	=B14+B11
B16	=B15+B11
C17	=SUM(C13:C16)
D17	=SUM(D13:D16)

 

[Lefty099]

Page 2

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			Date			Salesperson						Sales		Profit
2	CASH SALE	Supplier 1	15/01/2016 0:00	0	Z	Billy Ball ( 5723 )	CASH SALE (CASH)	Product 1	12	2936735	57	$1,000.00	B272	$100.00	6.91	5.82
3	CASH SALE	Supplier 1	15/01/2016 0:00	0	Z	Billy Ball ( 5723 )	CASH SALE (CASH)	Product 1	11	2936735	57	$375.00	B272	$10.00	11.52	15.71
4	CASH SALE	Supplier 1	7/01/2016 0:00	0	D	Bazza Brown ( 5641 )	CASH SALE (CASH)	Product 1	11	2929078	57	$276.00	B272	$150.00	119.96	-74.51
5	CASH SALE	Supplier 1	22/01/2016 0:00	0	Z	Bazza Brown ( 5641 )	CASH SALE (CASH)	Product 1	11	2940928	57	$154.00	B272	$10.00	44.94	0.47
6	CASH SALE	Supplier 1	9/01/2016 0:00	0	Z	Joe Blogs ( 5755 )	CASH SALE (CASH)	Product 1	11	2932247	57	$1,546.00	B272	$156.00	19.55	-1.4
7	CASH SALE	Supplier 1	10/01/2016 0:00	0	Z	Joe Blogs ( 5755 )	CASH SALE (CASH)	Product 2	11	2932853	57	$2,845.00	B272	$284.00	12.2	-0.86
8	CASH SALE	Supplier 1	10/01/2016 0:00	0	Z	Joe Blogs ( 5755 )	CASH SALE (CASH)	Product 2	12	2932853	57	$224.00	B272	$224.00	12.2	-0.86
9	CASH SALE	Supplier 1	19/01/2016 0:00	0	Z	Bazza Brown ( 5641 )	CASH SALE (CASH)	Product 2	11	2938872	57	$1,486.00	B272	$148.00	22.49	-13.4
10	CASH SALE.	Supplier 1	11/01/2016 0:00	944801	Z	John Citizen ( 5788 )	CASH SALE (CASH)	Product 2	11	2933528	57	$46,546.00	B272	$4,654.00	0	0
11	CASH SALE.	Supplier 1	12/01/2016 0:00	944801	Z	Billy Ball ( 5723 )	CASH SALE (CASH)	Product 2	11	2934176	57	$4,545.00	B272	$454.00	0	0
12	CASH SALE.	Supplier 1	11/01/2016 0:00	944869	Z	John Citizen ( 5788 )	CASH SALE (CASH)	Product 3	11	2933697	57	$1,235.00	B272	$123.00	0	0
13	CASH SALE.	Supplier 1	15/01/2016 0:00	944869	A	Billy Ball ( 5723 )	CASH SALE (CASH)	Product 3	12	2936371	57	$1,547.00	B272	$154.00	767.63	49.64
14	CASH SALE.	Supplier 1	15/01/2016 0:00	944869	A	John Citizen ( 5788 )	CASH SALE (CASH)	Product 3	13	2936371	57	$156.00	B272	$15.00	320	88.18
15	CASH SALE.	Supplier 1	15/01/2016 0:00	944869	Z	John Citizen ( 5788 )	CASH SALE (CASH)	Product 3	11	2936371	57	$651.00	B272	$65.00	0	0
16	CASH SALE.	Supplier 1	15/01/2016 0:00	945827	Z	Joe Blogs ( 5755 )	CASH SALE (CASH)	Product 3	11	2936363	57	$548.00	B272	$24.00	0	0
Sheet2

 

[Matty]

Thanks for providing the example. Can you now explain what, given the data you've provided, you need the formula to do and where it needs to go?

Matty