# Help Calculating Month to Date Based on Current Week

#### jssmn

##### New Member
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!

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
you could try sumifs - that allows for suming a range based on two conditions.

#### jssmn

##### New Member
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
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

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

##### Well-known Member
can you copy and paste the formula here

#### jssmn

##### New Member
can you copy and paste the formula here

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

Replies
1
Views
44
Replies
18
Views
575
Replies
11
Views
217
Replies
2
Views
134
Replies
8
Views
186

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.

### Which adblocker are you using?

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

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