Sumif Array

[Salar]

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

 

[pleeseemailme]

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]

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]

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.

 

[Salar]

pleeseemailme,

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