Help Calculating Month to Date Based on Current Week

jssmn

New Member
Joined
Jan 7, 2014
Messages
6
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
ABCDEF
127 | 52Test initiative
2Fiscal YearMonth2013
3Marketing InititativeStart DateEnd DateWeekActual Retail $
4Initiative112/30/121/5/1323 | 1 100
5Initiative11/6/131/12/1324 | 2 200
6Initiative11/13/131/19/1325 | 3 300
7Initiative11/20/131/26/1326 | 4 900
8Initiative21/27/132/2/1327 | 5 3,000
9Initiative22/3/132/9/1328 | 6 2,000
10
11Current Week is A1Formulas:
12Current Month is B1
13Week to Date 3,000
14Month to Date ?
15Year to Date 4,500

<tbody>
</tbody>
Sheet1
 
Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

dermie_72

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
you could try sumifs - that allows for suming a range based on two conditions.
 

jssmn

New Member
Joined
Jan 7, 2014
Messages
6
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

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
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

New Member
Joined
Jan 7, 2014
Messages
6

ADVERTISEMENT

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top