# SUMIF last n columns YTD

1. ## SUMIF last n columns YTD

Hello,

I'm building a tracking document with monthly data in which I need to calculate YTD this year and YTD last year (for the same period). Currently I am calculating the YTD TY and LY with the formulas below.

``Please Login or Register  to view this content.``
I'm hoping to be able to replace the range to be dynamic. Ideally I would like the TY YTD range to be the 12 columns directly to the left, and I would like the range for the LY YTD to be the 12 columns, to the left of this (OFFSET -12).

I appreciate any assistance, thanks!

2. ## Re: SUMIF last n columns YTD

See if the following formulas work for you:

for TY YTD, =SUMIFS(3:3,\$2:\$2,">="&DATE(YEAR(TODAY()),1,1),\$2:\$2,"<="&TODAY())
for LY YTD, =SUMIFS(3:3,\$2:\$2,">="&DATE(YEAR(TODAY())-1,1,1),\$2:\$2,"<="&TODAY()-400)

3. ## Re: SUMIF last n columns YTD

Z3=SUMPRODUCT((MONTH(\$B\$2:\$Y\$2)<=MONTH(\$D\$2))*(YEAR(\$B\$2:\$Y\$2)=LEFT(Z\$2,4)+0)*(\$B3:\$Y3)) copy across and down

4. ## Re: SUMIF last n columns YTD

Thanks, all!

There are currently 1 users browsing this thread. (0 members and 1 guests)