Help Calculating Month to Date Based on Current Week

[jssmn]

Hello,

I need to SUM sales for the current month, up to the current week.

In cell A1 I store the current fiscal week. In B1 I store the current month. I've tried a lot of ways to accomplish this. Week to date and Year to date were easy enough, but I can't figure out how to dynamically define a range of cells to sum based on two conditions.

The desired result for month to date would be 3,000. If the Week (A1) was 28 | 6, then the Month to Date result would be 5,000.

Thank you very much for your help!

Here is a link to download the spreadsheet and view WTD and YTD formulas: https://drive.google.com/file/d/0B9wQre-90qM6QV9mOGVUYkZqVTA

Excel 2010

	A	B	C	D	E	F
Fiscal Year
Marketing Inititative	Start Date	End Date	Actual Retail $
Initiative
Initiative
Initiative
Initiative
Initiative
Initiative
Formulas:

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #D8E4BC"]27 | 5[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: #D8E4BC, align: center"]Test initiative[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]Month[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]2013[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"][/TD]

[TD="align: center"]Week[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]12/30/12[/TD]
[TD="align: right"]1/5/13[/TD]
[TD="align: center"]23 | 1[/TD]
[TD="align: right"] 100[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1/6/13[/TD]
[TD="align: right"]1/12/13[/TD]
[TD="align: center"]24 | 2[/TD]
[TD="align: right"] 200[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1/13/13[/TD]
[TD="align: right"]1/19/13[/TD]
[TD="align: center"]25 | 3[/TD]
[TD="align: right"] 300[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1/20/13[/TD]
[TD="align: right"]1/26/13[/TD]
[TD="align: center"]26 | 4[/TD]
[TD="align: right"] 900[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1/27/13[/TD]
[TD="align: right"]2/2/13[/TD]
[TD="align: center"]27 | 5[/TD]
[TD="align: right"] 3,000[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2/3/13[/TD]
[TD="align: right"]2/9/13[/TD]
[TD="align: center"]28 | 6[/TD]
[TD="align: right"] 2,000[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFF00"]Current Week is A1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFFF00"]Current Month is B1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]Week to Date[/TD]
[TD="bgcolor: #FFFFCC, align: right"] 3,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]Month to Date[/TD]
[TD="bgcolor: #FFFFCC"] ?[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFCC, align: center"]Year to Date[/TD]
[TD="bgcolor: #FFFFCC, align: right"] 4,500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

 

[dermie_72]

you could try sumifs - that allows for suming a range based on two conditions.

 

[jssmn]

you could try sumifs - that allows for suming a range based on two conditions.

Thank you for your response. I wasn't familiar with that function.

How do I stop adding once it hits the current week? Do I need to include some sort of OFFSET function in the sumifs?

This is what I tried, but it doesn't work: =SUMIFS(F4:F10,B4:B10,B1,E4:E10,"<=A1")

 

[dermie_72]

i ended up adding a column G4-G9 that went:
=IF(D4<=INDEX($D$3:$D$9,MATCH($A$1,$E$3:$E$9,0),0),"X","")
Week to date = =SUMIFS(F:F,B:B,$B$1,G:G,"X")
Month to date = =SUMIFS(F:F,B:B,B1,G:G,"X")
Year to date = =SUMIF(G:G,"X",F:F)

See if that works for you.

 

[jssmn]

i ended up adding a column G4-G9 that went:
=IF(D4<=INDEX($D$3:$D$9,MATCH($A$1,$E$3:$E$9,0),0),"X","")
Week to date = =SUMIFS(F:F,B:B,$B$1,G:G,"X")
Month to date = =SUMIFS(F:F,B:B,B1,G:G,"X")
Year to date = =SUMIF(G:G,"X",F:F)

See if that works for you.

Hmm, I'm getting X's in all of the rows.

 

[dermie_72]

can you copy and paste the formula here

 

[jssmn]

can you copy and paste the formula here

User error. Thanks so much, this works perfectly. And thanks for teaching me sumifs