Sumproduct + Match + Offset?

[Overall_Confusion101]

I've been trying to use OFFSET formula nested within the ISNUMBER(MATCH) combination to get a dynamic range but can't get it to work. There's probably an easier and more elegant way to achieve what I'm trying to do to be honest but I thought about trying to see if this will work.

First version, without OFFSET, but selected range worked well:

=SUMPRODUCT(ISNUMBER(MATCH(ReturnsDataSheet[Return Date],P8:P37,0))*(ReturnsDataSheet[Project]='Control Check'!A83)*(ReturnsDataSheet[Company]='Control Check'!C83)*(ReturnsDataSheet[Company Type]='Control Check'!$B$78)*(ReturnsDataSheet[Return Type]='Control Check'!$A$78),ReturnsDataSheet[Contribution])

but when I have replaced the range with OFFSET, unfortunately, it stopped.

OFFSET on its own in a cell provides the correct range I'm interested in.

=SUMPRODUCT(ISNUMBER(MATCH(ReturnsDataSheet[Return Date],OFFSET(Quarters_Table[@Column3],0,0,$E$77,1),0))*(ReturnsDataSheet[Project]='Control Check'!A82)*(ReturnsDataSheet[Company]='Control Check'!C82)*(ReturnsDataSheet[Company Type]='Control Check'!$B$78)*(ReturnsDataSheet[Return Type]='Control Check'!$A$78),ReturnsDataSheet[Contribution])

Can OFFSET be used in this way at all?

 

[jasonb75]

OFFSET on its own in a cell provides the correct range I'm interested in.

Was that cell in the same row as the failing formula? With relative references to a different table things are always going to be messy.

Assuming that OFFSET(Quarters_Table[@Column3],0,0,$E$77,1) should refer to P8:P37 the formula would need to reside in row 8 for it to work. If the formula was in row 3 then it would refer to P3:P32 if it was in row 18 then it would return P18:P42 and so on. If the offset range falls outside of the table range then it will likely result in an error that will subsequently cause ISNUMBER to return FALSE and a zero result.

 

[Overall_Confusion101]

I knew it would be something simple that I have missed. Thanks, I've changed Quarters_Table[@Column3] to the first cell in that table and it has worked.