Sumifs/match/index?

Hi,

I have a table of data that I need to sum up but I am having difficulty figuring it out.

I've tried a combination of SUMIFS, MATCH and INDEX with OFFSETS but haven't been successful. The problem is that there are 2 vertical criteria in the problem.

I have a table made up of teams with date ranges along the top. These date ranges have past dates and future dates. The future dates are then split into 2 separate sections of confirmed and unconfirmed charges. I need to be able to return the sum of each section for each team individually using a lookup of some sort.

I have to use the whole range as a basis as the dates change and the separate ranges vary in size each week.

I've attached an example of my table.

Does anyone have any ideas?

Thanks in advance.

hi mstoto ,

Explain the same with an example .that will be better if you update the expected results manually for a typical case

Punnam

Pl see attached file with formula.

Hi Punnam,

I'm not sure exactly what sort of example you are after.

The attached table is system generated. It is downloaded on a weekly basis. The dates at the top will change each week, e.g. this week, the first column dated 10/10/2014 in the "Confirmed" and "Unconfirmed" (both of these are future dates) will be part of the "Actuals" next week. The "Actuals" will increase week by week and the "Confirmed" and "Unconfirmed" sections will vary in size depending on if we have any forecast figures for any given week. This means that the last 2 sections can vary in size regardless of which week we are in.

I use this data to sum up each section for each team. Doing this manually is tedious and takes time. I wanted to create a formula that will sum up the "Actuals", "Confirmed" and "Unconfirmed" numbers for each week in a separate table.

I hope this makes sense.

Thanks.

Hi mstoto

I wanted to know how should be your result look like And any dummy table in a separate sheet

Punnam

Hi All,

I've tried all formulas and managed to resolve the issue.

Thanks for all your help guys.