Sum the last column depending on current date

[Zyphon]

Hi Everyone,

I wonder if someone can help me with a summing issue I am having.

I have a KPI spreadsheet where I enter monthly figures From April 2015 to March 2016 (UK Financial Year) and at the end is a total column which in this case is a 'snapshot' at the end of the period.

In row 2, columns E:P are the months from April to March (UK Financial Year) in date format 'mmm'. Each month is the last date of that month for example E2 will be 30/04/2015. I use this row to calculate YTD figures for other months such as:

Please Login or Register  to view this content.

What I need is a formula that will look at the current date (or Today's date) and match that to the dates in row 2 and return the value of that cell. For example:

Today is 22/07/2015 and we we are not at the end of July the formula should return the value in the June column. If today's date was the 31st July or greater and then it would return the value in the July colum. I hope this makes sense.

I figured a formula similar to above with perhaps an OFFSET formula but I can't get my head around how this would work.

I would really appreciate any help anyone can offer.

Many thanks.

[cbatrody]

Hi,

Can you post a sample excel file please?

[Zyphon]

Hi cbatrody,

Thanks for your help. I have attached a sample.

If you look at the attached spreadsheet in rows 4 and 5 in column Q you will see the total calculation. I am using the OFFSET function but it's not working and ideally I need to match the month in columns E:P in row 2.

If you look at cell Q8 there is a formula in there that sums all values from column E to whatever today's date is and matches the end column.

So ideally the formula in Q4 should return 43, and the formula in Q5 should return 97%.

I would be very grateful if you could help me out.

Many thanks.

EDIT: I Should add these spreadsheets are linked to a master spreadsheet and if there is no value in a cell the master spreadsheet returns zero so that is why I need to match the date in row 2 and not offset the last completed cell.

Thanks.

[cbatrody]

Hi Zyphon,

Try the following formula in Q4:

=INDEX(E$2:P$5,3,MATCH(IF(TODAY()=EOMONTH(TODAY(),0),TODAY(),EOMONTH(TODAY(),-1)),$E$2:$P$2,0))

& Q5:
=INDEX(E$2:P$5,4,MATCH(IF(TODAY()=EOMONTH(TODAY(),0),TODAY(),EOMONTH(TODAY(),-1)),$E$2:$P$2,0))

See the attached file!

[Zyphon]

Hi cbatrody,

Wow! That was really fast. Thank you ever so much for the formula above. It works great.

I take it that you simply change the $P$5 to whatever row you want?

Thanks again I really appreciate your help.

[cbatrody]

Yes, That's correct!

If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

Also, you can directly thank those who have helped you by clicking on the small * (star) icon located in the lower left corner of a post that you have found to be helpful.

[Zyphon]

Thanks again for your help. Also I have posted this thread as solved and added to your reputation.