SUMIFS and OFFSET

[AllisterB]

Hi

I am using a SUMIFS. One criteria is a cell in the left top corner which I have made absolute cell ref.. Each time I copy the formula I want the formula to use the criteria in the relative position.

On the attached the second instance of the SUMIFS has 102 in cell m4 whereas the first instance had it in H4. I want the second instance to use the cell containing the 102

Does teh solution involve OFFSET ? - if so how ?

Thank You

[windknife]

I6
=SUMIFS(Table1[Value],Table1[Dept],LOOKUP(2,1/($A$4:I$4<>""),$A$4:I$4),Table1[FY],I$5,Table1[Item],$H6)

J6
=SUMIFS(Table1[Value],Table1[Dept],LOOKUP(2,1/($A$4:I$4<>""),$A$4:I$4),Table1[FY],J$5,Table1[Item],$H6)

[Bo_Ry]

Please try at
I6

=SUMIFS(Table1[Value],Table1[Dept],MAX(G4:J4),Table1[FY],I5:J5,Table1[Item],$H$6:$H$9)

[AllisterB]

A usual Bo_Ry a nice elegant solution.

I am curious as to what part of the Formula generates the Dynamic Array?

Allister

[Bo_Ry]

Red part

=SUMIFS(Table1[Value],Table1[Dept],MAX(G4:J4),Table1[FY],I5:J5,Table1[Item],$H$6:$H$9)

[AllisterB]

Thank You Bo_Ry - great to understand this