# Sumif Array

#### Salar

##### Board Regular
I am trying to create a spreadsheet to track weekly, monthly, & YTD project costs. The spreadsheet is a list of projects in column A and the budget available per project in column B. The next 52 columns are setup with dates along the heading ie. 3rd January, 10th January, 17th January and so on to the end of the year. Data is entered weekly.

I am looking to work out two totals on is current spend to date which I can do using the sum function.

The second total is for the Current Month ie. sum for January. Then when it becomes Febuary the column resets and starts again untilit is March.

Any help would be appreciated.

I am currently using 2003, but moving to 2010 shortly

Thanks
Salar

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### pleeseemailme

##### Board Regular
Salar,

Can the column headings (3rd January, etc...) currently entered as text? If so, could they be changed to be entered as dates? ie 1/3/2010, 1/10/2010

#### Salar

##### Board Regular
Yes no reason why not.

Salar,

Can the column headings (3rd January, etc...) currently entered as text? If so, could they be changed to be entered as dates? ie 1/3/2010, 1/10/2010

#### pleeseemailme

##### Board Regular
Salar,
The easiest way I see to to this would be to do the following. Add two hidden helper rows either above or below your dates. In the first row enter =MONTH(B1) and fill across. Do the same in the second row with =Year(B1). Then, assuming that the monthly sum number will be placed in the same row as the budget data enter:

=SUMIFS(\$B4:\$O4,\$B\$2:\$O\$2,MONTH(TODAY()),\$B\$3:\$O\$3,YEAR(TODAY())) where row two is the same as the row with the MONTH function in it and row three is the same as the YEAR function. You can then fill this down for every project in your sheet.

Once a new month hits, the SUMIFS formula will update to the current month.

Last edited:

#### Salar

##### Board Regular
pleeseemailme,

Thanks for your help. I will give it a try and let you know the outcome

Replies
2
Views
91
Replies
4
Views
138
Replies
12
Views
409
Replies
9
Views
344
Replies
19
Views
412

1,171,463
Messages
5,875,656
Members
433,145
Latest member
nzltrippa

### 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?

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