Year-to-date year to date formula

[Philm]

Hi All
I have a formula that gives me year-to-date on an incomplete
range A to L..

=SUM(OFFSET(A2:AL,0,0,1,MATCH(M1,A1:L1,0)))

So incoming data
A3 = 3
B3 = 4
C3 = 5

etc....

To go with is I have targets completed to L

A2 = 3
B2 = 3
C2 = 3
etc

And months (working on a July - June year)

A1 = Jul
B1 = Aug
C1 = Sep
etc

Problems

We are now in October so we have figures to date
But I also want the targets added up to date not the whole year
which the above formula does.


1) I would like the formula to work from todays date eg NOW() but match
cant seem to handle date types (cell format = mmm)

2) formula works fine on incomplete rows but in the case of target
takes the whole row.

So is there another formula that
a) allows for date comparisons given the formula above
b) copes with full rows (so I only want Jul to October added in the
above senario)

Cheer

Phil

 

[Max]

Assuming in A1:L1 are real "1st of month" dates:
01-Jul-05, 01-Aug-05, 01-Sep-05, ...

and targets completed are in A2:L2 : 3,3,3,3 ...

Put in M1: =DATE(YEAR(TODAY()),MONTH(TODAY()),1)

Then with your formula in say, N1:
=SUM(OFFSET(A2:AL2,0,0,1,MATCH(M1,A1:L1,0)))

it'll will return the sum of targets in A22, i.e.: 12