YTD Formulas

[Winne13]

I'm trying to compare my YTD sales figures with sales figures from the past few years. I'm trying to use the SUMIF function as follows:
=SUMIF(P$94:P$206,"<=" &TODAY()-1,S94:S206)
Where column P is dates and column s is sales. That works peachy keen for the current year. But when I try to adjust it for previous years, like:
=SUMIF(U$45:U$206,"DATE(YEAR(S209)-1,MONTH(S209),DAY(S209))",X45:X206)
where column U is dates, x is sales figures, and s209 is a cell showing todays date, minus one I'm getting zeros. I'm thinking formatting error, but I doubled checked and everything is mm/dd/yy. . . .
Maybe there's an easier way?

[gak67]

What version of excel are you using?

[gak67]

First off, the way you have the double quotes isn't right. Try

Please Login or Register  to view this content.

or if you're using 2007 or later

Please Login or Register  to view this content.

[Winne13]

Excel for Mac 2011

[Winne13]

The second formula works for last year, can I adjust it to 24 months for the previous year?

[gak67]

Ok, the EDATE function should work for you then.

[gak67]

Yes you can. the second argument of the function is the number of months to offset from the date specified in the first argument. Positive numbers to move forward and negative numbers to move backwards.