Sumif Array

Salar

Board Regular
Joined
Mar 19, 2008
Messages
122
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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

pleeseemailme

Board Regular
Joined
Dec 26, 2013
Messages
201
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
Joined
Dec 26, 2013
Messages
201
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
Joined
Mar 19, 2008
Messages
122
pleeseemailme,

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

Forum statistics

Threads
1,137,060
Messages
5,679,376
Members
419,824
Latest member
Mercy kiara

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