Sum a certain range based off date

[greatcookies]

I have a spreadsheet that contains dally sales in column K with the dates in column F. What I need is to get a MTD sum of sales and a YTD sum of sales based off the date, which will be in a separate sheet, cell C3. What I am thinking is a VLookup formula using the date in cell C3 as a reference. Once it finds this it will sum all sales above for that month. I am not sure, but you may have to have a formula for each month and use the 1st of that given month as a reference.

So, for example, let's say I want to get sales MTD for February, using 02/12/18 in cell C3 as my beginning point. February 1st sales are in K1129, so would I use a Vlookup and then sum sales from that point to K1129?

Any help in getting me started is much appreciated.

Thanks

 

[Scott T]

Change cell references to match your ranges

Excel 2010
	B	C	D	E	F	G	H	I	J	K
1					date					sales
2					12/30/2017					99
3					1/2/2018					1
4		2/20/2018			1/3/2018					2
5					1/4/2018					3
6	mtd	810			1/5/2018					4
7	ytd	1275			1/6/2018					5
8					1/7/2018					6
9					1/8/2018					7
10					1/9/2018					8
11					1/10/2018					9
12					1/11/2018					10
13					1/12/2018					11
14					1/13/2018					12
15					1/14/2018					13
16					1/15/2018					14
17					1/16/2018					15
18					1/17/2018					16
19					1/18/2018					17
20					1/19/2018					18
21					1/20/2018					19
22					1/21/2018					20
23					1/22/2018					21
24					1/23/2018					22
25					1/24/2018					23
26					1/25/2018					24
27					1/26/2018					25
28					1/27/2018					26
29					1/28/2018					27
30					1/29/2018					28
31					1/30/2018					29
32					1/31/2018					30
33					2/1/2018					31
34					2/2/2018					32
35					2/3/2018					33
36					2/4/2018					34
37					2/5/2018					35
38					2/6/2018					36
39					2/7/2018					37
40					2/8/2018					38
41					2/9/2018					39
42					2/10/2018					40
43					2/11/2018					41
44					2/12/2018					42
45					2/13/2018					43
46					2/14/2018					44
47					2/15/2018					45
48					2/16/2018					46
49					2/17/2018					47
50					2/18/2018					48
51					2/19/2018					49
52					2/20/2018					50
Sheet1

Cell Formulas
Range	Formula
C6	=SUMPRODUCT(--(F2:F61<=C4),--(F2:F61>=DATE(YEAR(C4),MONTH(C4),1)),K2:K61)
C7	=SUMPRODUCT(--(F2:F61<=C4),--(F2:F61>=DATE(YEAR(C4),1,1)),K2:K61)

 

[greatcookies]

Thank you! Works perfectly. I appreciate your help.

 

[Scott T]

You are welcome