ADD YTD Based on Dynamic Month

**CrocHntr** · 07-21-2016, 01:57 PM

I hope this is an easy one. I have a file (starting in A1) with 12 months across the top. I want to dynamically add year-to-date summary column depending on which month I identify. So, if I hard code in a cell JUL (or 07), I want to find a formula that will add each column up to the JUL column. I thought about using a HLOOKUP to do so, but that will only get me the current month selected (not year-to-date). Is there a good formula or trick I can use so that I can type the month in a cell and the formula will calculate YTD based on the cell input?

YTD.jpg

Thanks for the help!

**protonLeah** · 07-21-2016, 02:07 PM

deleted....

**6StringJazzer** · 07-21-2016, 02:09 PM

First, it's nearly always better to attach your file rather than a screenshot.

Are the months dates, or just strings?

There are a few different ways to do this. If your hard-coded month name is in, say, A20, and your monthly totals are in row 11 here is one way:

Formula:

Please Login or Register  to view this content.

**newdoverman** · 07-21-2016, 02:59 PM

Here is a slight correction to the formula by 6StringJazzer.
=SUM(OFFSET(A11,0,0,1,MATCH(A20,A1:L1,0)))

Here is your example data in a file so that you can "play around" with it.

**CrocHntr** · 07-21-2016, 04:27 PM

Fantastic! I apologize for not attaching an excel working file. The example attached works perfectly though. Just what I needed. I hadn't used the offset function before. I very much appreciate the help by both of you!