Help with formula in excel to calculate up to current month

[Juddy58]

Hello,
I have a sheet "maintenance" with a cell that contains the current month and year
eg Jun 2004
i have another sheet with a column for each month of the financial year eg
Jul 2004 Aug 2004 Sep 2004 Oct 2004 Nov 2004 through to Jul 2005
StaffHours
What i need to do is calculate up to the current month in another sheet
eg if the current month is Aug 2004 it would add Jul 2004 and Aug 2004 or if it was Nov 2004 then it would calcualte the sum of Jul Aug Sep Oct and November for each row. Just wondering if there is a formula that can do this.
Thanks Justin

 

[SkipVought]

Justin,

Are your "date" cells REAL DATES? You can determine if a cell contains a REAL DATE is the Cell/Format is a Date Format AND if you CHANGE the Format to GENERAL, the Date becomes a NUMBER. You need to have REAL DATES. REAL DATES can be FORMATTED to look just like Jun 2004

Lets suppose that the Dates on StaffHousr are in A1:L1 and the Hours are in A2:L12

Then the SUM of Hours thru the CurrentMonth is
[tt]
=SUMPRODUCT((A1:L1<=Maintenance!A1)*(A2:L2))
[/tt]
where Maintenance!A1 is =Today()

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[byundt]

Let's assume that the first column to be added is Sheet2 column B, and that cell A1 contains the current month and year. Sheet2 row 1 contains the month and year header, and the data starts in row 2. The index number of the last column in Sheet2 to be added is:
=MATCH($A$1,Sheet2!$B$1:$IV$1,0)

The requested sum formula for row 2 then becomes:
=SUM(OFFSET(Sheet2!$B2,0,0,1,MATCH($A$1,Sheet2!$B$1:$IV$1,0)))

 

[Juddy58]

Thanks for the replys, i will have a play around and let you know how i go!
Justin