sum certain columns

[geebee]

hi,

i have a sheet with severasl rows in it. each row corresponds to a certain
month and year. ranging from jan-04 through mar-08. i would like to add a
new column to the worksheet, and have it have a formula for the YTD sum. so
how can i make it to where the new column only adds the amounts for months in
the current year? can this be programatically done?

thanks in advance,
geebee

 

[Bernie Deitrick]

geebee,

With dates in column A, and values to sum in column B, both starting in Row 2 (headers in row 1) in
C2 use the formula

=SUMPRODUCT((YEAR($A$2:$A2)=YEAR(A2))*$B$2:B2)

And then copy down to match your data set. This assumes that the dates are sorted ascending.

HTH,
Bernie
MS Excel MVP

 

[Joel]

This is the formula I did

=SUM(OFFSET($A$1,ROW()-1,MATCH(DATEVALUE("Jan-"&TEXT(YEAR(TODAY()),"YY")),$1:$1,1)-1,1,1):OFFSET(BA9,0,-1))

It uses cell A1 as the reference. It finds the data Jan 1 for the current
year and then adds all the data in the current row from this column to the
column - 1 where the formula is located. In my case the column for the YTD
is BA and the row it is in is row 9. This can be added via VBA

Range("BA9").formula = Myformula

where myformula is "=......." 'the formula shown above. Make sure the equal
sign is in Myformula