OFFSET returns a reference to a range that is a given number of rows and columns away from a starting range:
=OFFSET(reference, rows, cols, [height], [width])
- rows > 0 — down, < 0 — up; cols > 0 — right, < 0 — left;
- height/width — size of the returned range (defaults to the size of the reference).
Example from the manual: sales per department
Departments are column headings, so SUMIFS is awkward. Take the first data column and shift it right by the department's position found with MATCH:
=SUM(OFFSET($B$2:$B$7,0,MATCH(G2,$B$1:$E$1,0)-1))
With a row condition too, via SUMPRODUCT:
=SUMPRODUCT(OFFSET($B$2:$B$7,0,MATCH(G2,$B$1:$E$1,0)-1)*($A$2:$A$7=I$1))