formula as criteria in SUMIFS

I am trying to use an INDEX:MATCH formula as a criteria in a SUMIFS formula.

Formula:

=SUMIFS(DE_Subtotal,DE_Date,(INDEX(Report_DateRef,MATCH('Monthly Report'!$B$3,Report_Month,0))),DE_Date,(INDEX(Report_DateRef2,MATCH('Monthly Report'!$B$3,Report_Month,0))),DE_Line,'Monthly Report'!$B$5)

As you can see two of the criteria (the problem ones) are INDEX:MATCH formulas to look up a value. However, the complication is that the lookup value contains a formula itself- the values are a variation on this: ">="&DATE(2014,1,1) contained within another cell.

If I replace the index:match formula with the values (">="&DATE(2014,1,1)) the formula works, so it is just a problem with inseerting it using INDEX:MATCH

Is this possible, any ideas for a way to work around it?

Thanks

Could you post a sample workbook?

Unfortunately cannot do a sample workbook, apologies, however the return is two date ranges that pull out data within a month, so for example:

">="&DATE(2014,1,1) and "<"&DATE(2014,2,1)

thanks

">="&date(2014,8,1)

What he means is, paste his formula into your worksheet and post back what the result of that formula is.

sorry misunderstood, see above I edited the post

Sorry but it would just to take to long to change all the data for a sample.

I used that method because the text strings are needed elsewhere and, where possible, I like to keep things standardised.

However, I tried using actual dates and putting the >/< symbols into the formula itself, but again this doesn't work. This is the formula I used...

=SUMIFS(DE_Subtotal,DE_Date,">="&(INDEX('Helper Cells'!J4:J15,MATCH('Monthly Report'!D4,Report_Month,0))),DE_Date,(INDEX('Helper Cells'!K4:K15,MATCH('Monthly Report'!D4,Report_Month,0))),DE_Line,'Monthly Report'!$D$6)

Again when I removed the INDEX:MATCH and just put in ">="& and directly referenced the cell with the date in it calculates the data fine

So again I am confused

I have marked the thread as solved because I have managed to work around it using a completely different method.

Without a sample workbook an explanation just seem valuable or worthwhile, so I will leave it that - thanks for your help anyway!

Please read above comment, I have resolved it. Thank you

The method I used was not related to the problem in the thread so is irrelevant