Sum up rows up until current month

[wormaldp]

Hi everybody,

I've got a slightly tricky problem that consists of two parts.

The first part is I need a formula that returns not the last day of the month, but the last business day of the month.

The second part is - I have a spreadsheet with information about the performance/fees of a fund. It also has a summary table at the bottom showing a few useful things eg the gross performance of a fund. At the moment, that value is just worked out by adding the gross performance of the fund by month (eg =sum(e8:aa8)). However, I want to change the second part (aa8) to sum up until the column, that contains last months data (the header for last month will be 28-Nov-14). How do I do this?!

(So just to clarify, I think I will probably have to have a cell where today's date is, and then the formula will read off that date)

Let me know.

Thanks,

Peter

 

[Jonmo1]

For the first part...

The last business day of the month is

the first day of the NEXT month, minus 1 business day.
So with any given date in A1
=WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),-1)

Hang on while I read the 2nd part...

 

[Jonmo1]

Assuming the 'Header' is in Row 1 and contains real dates...
And let's assume cell A2 contains the formula I posted before that gives the last business day.

You can find the 'last business day' in Row 1 with
=MATCH(A2,A1:AA1,0)

And use that in an INDEX of Row 8 in your SUM
=SUM(A8:INDEX(A8:AA8,MATCH(A2,A1:AA1,0)))


Hope that helps.

 

[wormaldp]

Amazing! Thank you so much Jonmo1 - that works perfectly!

Much appreciated

 

[Jonmo1]

Glad to help, thanks for the feedback.