Sum MTD and YTD formulas

[sachavez]

Excel 2010

Looking for some help on two formulas:

Sum MTD units
Sum YTD units

Column A contains dates
Column B contains units

Date data starts in cell A3
Unit data starts in cell B3

MTD formula will be in cell A1
YTD formula will be in cell B1

Data set grows daily, but it's at "table" so I ***think*** the formula would be dynamic?

Thanks in advance!

Steve

 

[sheetspread]

The pivot table does this easily, or must you have a formula?

 

[sachavez]

The pivot table does this easily, or must you have a formula?

Formula

 

[sachavez]

I was able to get the YTD formula

=sumif(A3:A1000,"<="&TODAY(),B3:B1000)

Works well, just need MTD formula

 

[sheetspread]

Something like this?

Excel 2010
	A	B
1
2	Date	Number
3	9/3/2015	3
4	9/7/2015	3
5	9/11/2015	4
6	9/15/2015	2
7	9/19/2015	11
8	9/23/2015	5
9	9/27/2015	5
10	10/1/2015	8
11	10/5/2015	11
12	10/9/2015	3
13	10/13/2015	1
14	10/17/2015	7
15	10/21/2015	5
16	10/25/2015	6
17	10/29/2015	7
18	11/2/2015	8
19	11/6/2015	11
20	11/10/2015	7
21	11/14/2015	10
22
23	MTD	36
24	YTD	117
Sheet8

Cell Formulas
Range	Formula
B23	=SUMPRODUCT(--(MONTH($A$3:$A$21)=MONTH(TODAY())),$B$3:$B$21)
B24	=SUMPRODUCT(--(YEAR($A$3:$A$21)=YEAR(TODAY())),$B$3:$B$21)

 

[konew1]

You need to have a current date somewhere. I put it in E1, but you could include it in the formula
A1=SUMIFS(Table1[units],Table1[date],"<="&$D$1,Table1[date],">"&EOMONTH($E$1,-1))
B1==SUMIFS(Table1[units],Table1[date],"<="&$D$1,Table1[date],">"&EOMONTH($E$1,-MONTH($E$1)))

 

[sachavez]

Pretty slick! Thank you.

Now, how do I get same info for years 2013 & 2014?

Something like this?

Excel 2010
	A	B
1
2	Date	Number
3	9/3/2015	3
4	9/7/2015	3
5	9/11/2015	4
6	9/15/2015	2
7	9/19/2015	11
8	9/23/2015	5
9	9/27/2015	5
10	10/1/2015	8
11	10/5/2015	11
12	10/9/2015	3
13	10/13/2015	1
14	10/17/2015	7
15	10/21/2015	5
16	10/25/2015	6
17	10/29/2015	7
18	11/2/2015	8
19	11/6/2015	11
20	11/10/2015	7
21	11/14/2015	10
22
23	MTD	36
24	YTD	117
Sheet8

Cell Formulas
Range	Formula
B23	=SUMPRODUCT(--(MONTH($A$3:$A$21)=MONTH(TODAY())),$B$3:$B$21)
B24	=SUMPRODUCT(--(YEAR($A$3:$A$21)=YEAR(TODAY())),$B$3:$B$21)

 

[sachavez]

Checking this option, too. Appreciate the help.

Steve

You need to have a current date somewhere. I put it in E1, but you could include it in the formula
A1=SUMIFS(Table1[units],Table1[date],"<="&$D$1,Table1[date],">"&EOMONTH($E$1,-1))
B1==SUMIFS(Table1[units],Table1[date],"<="&$D$1,Table1[date],">"&EOMONTH($E$1,-MONTH($E$1)))

 

[sheetspread]

Excel 2010
	A	B	C	D
1
2	Date	Number
3	9/3/2013	3
4	10/3/2013	3
5	11/2/2013	4
6	12/2/2013	2
7	1/1/2014	11
8	1/31/2014	5
9	3/2/2014	5
10	4/1/2014	8
11	5/1/2014	11
12	5/31/2014	3
13	6/30/2014	1
14	7/30/2014	7
15	8/29/2014	5
16	9/28/2014	6
17	10/28/2014	7
18	11/27/2014	8
19	12/27/2014	11
20	1/26/2015	7
21	11/25/2015	10
22
23		2015	2014	2013
24	MTD	10	8	4
25	YTD	17	77	10
Sheet8

Cell Formulas
Range	Formula
B24	=SUMPRODUCT(--(YEAR($A$3:$A$21)=B$23),--(MONTH($A$3:$A$21)=MONTH(TODAY())),$B$3:$B$21)
B25	=SUMPRODUCT(--(YEAR($A$3:$A$21)=B$23),--(MONTH($A$3:$A$21)<=MONTH(TODAY())),$B$3:$B$21)

 

[sachavez]

So close!

New formulas resulting in full year and full month numbers.

Example:

In year 2014, YTD (1/1/14 - 11/12/14) actual is 3,325, while the formula is producing 217,058 units (this is the actual "full year" number).
In year 2014, MTD (11/1/14 - 11/12/14) actual is 67, while the formula is producing 19,508 units (this is the actual for the "full month" number)

Excel 2010
	A	B	C	D
1
2	Date	Number
3	9/3/2013	3
4	10/3/2013	3
5	11/2/2013	4
6	12/2/2013	2
7	1/1/2014	11
8	1/31/2014	5
9	3/2/2014	5
10	4/1/2014	8
11	5/1/2014	11
12	5/31/2014	3
13	6/30/2014	1
14	7/30/2014	7
15	8/29/2014	5
16	9/28/2014	6
17	10/28/2014	7
18	11/27/2014	8
19	12/27/2014	11
20	1/26/2015	7
21	11/25/2015	10
22
23		2015	2014	2013
24	MTD	10	8	4
25	YTD	17	77	10
Sheet8

Cell Formulas
Range	Formula
B24	=SUMPRODUCT(--(YEAR($A$3:$A$21)=B$23),--(MONTH($A$3:$A$21)=MONTH(TODAY())),$B$3:$B$21)
B25	=SUMPRODUCT(--(YEAR($A$3:$A$21)=B$23),--(MONTH($A$3:$A$21)<=MONTH(TODAY())),$B$3:$B$21)