Automatically Update Sum Rannge

[DALLOW]

Hey everyone...I've trawled around the site but can't find an answer to my specific problem. Hopefully someone can help!

I would like to calculate the Year To Date total without having to manually adjust the sum range. For instance in Jan the YTD calc is just col M; in Feb the YTD calc is sum(L:M); in March, sum(K:M) etc.

M will remain constant in the formula but I need to find a way looking up the data in Jan column which moves every month. I think HLOOKUP is the way to go but the variations of hlookup, offset, column combinations I've tried have not been successful.

Hope you guys can help.

Thanks

 

[Jonmo1]

Welcome to the board..

Do you want it to be based on just today's current month?

Try

=SUM(OFFSET($M2,0,1-MONTH(TODAY()),1,MONTH(TODAY())))

 

[DALLOW]

Thanks for the speedy reply.

I've tried to adjust your formula but with no success. I am not using calendar months as the data is labelled with day,month and year and is text rather than Date formatted.

 

[Jonmo1]

why did you try to adjust it?

It doesn't matter how your data is labled, it's based on whatever the current real world month is.

The only thing you should udjust is maybe the row#
=SUM(OFFSET($M2,0,1-MONTH(TODAY()),1,MONTH(TODAY())))

 

[DALLOW]

Thanks I've tried it again and it works !!!

Who knows what I did the first time.

Thanks again

 

[Jonmo1]

Glad to help, thanks for the feedback.