SUMIF and HLOOKUP?

[luciefialova]

Hi,

I've been racking my brains for a while now, trying to find an elegant solution to this. I've connected my spreadsheet to the database to pull through orders for some booklets. These then go into a summary table that has months as columns and booklet titles as rows. I've written a bunch of nested IFs and VLOOKUPs to get me either a sum of orders for months that have happened, or calculate a prediction for future months. So the table holds figures for Jan through to Dec.

In another column I want to calculate a variance against 2014 for year to date. I have a field that tells me what month it is today. What I want to do is write a formula that looks at that cell, then goes and finds the figures for up Oct in the table and calculates these. Of course I could just recalculate it every month, but I want it to update itself. I've tried to nest a HLOOKUP and INDEX-MATCH in a SUMIF, but I just can't get to the bottom of it. Anybody have an idea? THANKS.

[AliGW]

You really need to attach the workbook here for someone to look at.

[luciefialova]

Here goes. Not sure it will work though with the data connections.