relative reference in an array formula that refers to a table

[TVTony]

I'm sure someone else must have run into this issue before me, but so far I can't find the answer.

So here's my problem. I've got an array formula that includes a reference to a table:

=SUM(IF(MyTable[[#All],[Column72]:[Column72]]=Sheet2!$A2,MyTable[[#All],[Column10]]))

If I put this in cell A1, use SHIFT+CTRL+ENTER, then drag it across B1, C1, etc, I'd like the reference in B1 to become [Column11], in C1 to be [Column12], and so on. Instead, it stays [Column10], ie, an absolute reference.

This seems to be an array formula issue. If I just hit ENTER instead of SHIFT+CTRL+ENTER, then the reference is relative and I get [Column 11] etc. But that's not much use to me since this needs to be an array formula.

I've found resources like https://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/ but it doesn't seem to address my issue, because it discusses relative/absolute references to tables in 'standard' (non-array) formulas only.

 

[TVTony]

PS, maybe I shouldn't have quoted my formula with

; obviously it's not meant to be VBA.

 

[TVTony]

Oh, and before someone asks (quite sensibly) why I don't simply use a SUMIF formula rather than an array formula, it's because what I really want to do is this:

=SUM(IF(MyTable[[#All],[Column72]:[Column72]]=Sheet2!$A2,MyTable[[#All],[Column10]:[Column12]]))

...that is, sum a multi-column/multi-row range. You can do that with SUM, but apparently not with SUMIF or SUMIFS...hence the need for the array formula. Sorry I didn't make that clear in the original post.

 

[StephenCrump]

Try A1: {=SUM(IF(MyTable[Column72]=Sheet2!$A$2,OFFSET(MyTable,,MATCH("Column10",MyTable[#Headers],)+3*COLUMNS($A1:A1)-4,,3)))}

This assumes Column10:Column12 is contiguous, and that you want B1 to sum adjoining columns Column13:Column15 (?)

You can adjust the COLUMNS() reference to suit, e.g. if B1 should be sum of Column11:Column13, change

3*COLUMNS($A1:A1)-4
to COLUMNS($A1:A1)-2