Sumifs for row and column and indirect in one

[nilani]

Hello,
I wondered if someone could help me please. I am trying to set up a management accounts report and am having some problems getting data to pull through in the way that I need it. I have attached a sample file of what I'm trying to do. I have lots of data split by departments and business units for actuals and budgets. I want to be able to select the department and the worksheet automatically populates the summary page with the relevant figures for that department. So in the attached, I want to fill in the budget column (COLUMN C - in summary sheet) based on criteria in A1 and C1, using data on budget sheet. I have given each department set of figures a name but this doesn't appear to have helped.
In column G I want to be able to select a period so in Feb, the total will be Jan + Feb and so on.

Would be grateful if anyone could shed some light on how I could approach this. I've tried, lookups, sumifs, but nothing seems to be working.

Thank you for your help

N

[Beamernsw]

Hi nilani, try this in cell C5 of the Summary sheet.

Please Login or Register  to view this content.

Add some figures into Revenue on the Budget sheet. When you change Company/Dept, or change month it should work accordingly.
Then modify it to suit your other cells.

Edit:
I was just thinking about that formula, and if column B isn't always Jan (Maybe when you cross over from 2015-2016 for instance) or if your data continues more than 12 columns then it won't work.
This formula is the same except it replaces the MONTH part with a search for the date within the columns A-M on the Budget sheet.

Please Login or Register  to view this content.

[Beamernsw]

Actually, scrap all that. The whole CHAR thing could be a mistake, I think using OFFSET would be better and safer.

Please Login or Register  to view this content.

[MarvinP]

Hi nilani,

After looking at your sample data, I think you need to read
http://www.timeatlas.com/excel-pivot-tables/

The idea is that Excel is a "tables based" system where each column has similar data and each row is one record of that data. Excel is amazing if you start with a format of your data that it can work with.

[nilani]

Thank you!