previous year
 

hi,

i have columns of data in my sheet, for 2008, each with a column heading in
the MMM-YY format. my formula worked for all months in 2008, but now that we
have transitioned to 2009, it is not adding the values columns for the months
in 2008. here is my formula:

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

i am not sure how to amend this formula so it works this month...

thanks in advance,
geebee

previous year
 

Tell us more about the data - show us a subset of column A
Tell us what you wish to achieve with the formula in terms of the subset of
data
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"geebee" (noSPAMs) wrote in message
...
hi,

i have columns of data in my sheet, for 2008, each with a column heading
in
the MMM-YY format. my formula worked for all months in 2008, but now that
we
have transitioned to 2009, it is not adding the values columns for the
months
in 2008. here is my formula:

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

i am not sure how to amend this formula so it works this month...

thanks in advance,
geebee

previous year
 

If you want months regardless of years adapt this idea, assuming real dates
=sumproduct((month(a2:a22=2)*1) to count Jan
=sumproduct((month(a2:a22=2)*b2:b22) to sum col b for Jan in col A

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"geebee" (noSPAMs) wrote in message
...
hi,

i have columns of data in my sheet, for 2008, each with a column heading
in
the MMM-YY format. my formula worked for all months in 2008, but now that
we
have transitioned to 2009, it is not adding the values columns for the
months
in 2008. here is my formula:

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

i am not sure how to amend this formula so it works this month...

thanks in advance,
geebee