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
1	27 | 5	2	Test initiative
2	Fiscal Year	Month				2013
3	Marketing Inititative		Start Date	End Date	Week	Actual Retail $
4	Initiative	1	12/30/12	1/5/13	23 | 1	100
5	Initiative	1	1/6/13	1/12/13	24 | 2	200
6	Initiative	1	1/13/13	1/19/13	25 | 3	300
7	Initiative	1	1/20/13	1/26/13	26 | 4	900
8	Initiative	2	1/27/13	2/2/13	27 | 5	3,000
9	Initiative	2	2/3/13	2/9/13	28 | 6	2,000
10
11	Current Week is A1		Formulas:
12	Current Month is B1
13			Week to Date	3,000
14			Month to Date	?
15			Year to Date	4,500

<tbody>
</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