Year-to-date year to date formula
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 |
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 A2:D2, i.e.: 12 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Philm" wrote in message oups.com... 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 |
All times are GMT +1. The time now is 02:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com