Excel formula for dragging down a row to move across a column

Question

I'm wanting a sumifs formula such that when I drag it down a row, the sumrang jumps across a column (in a different tab).

For simplicity, say in cell B2 I hav:

=Sumif(array, criteria, 'Sheet 1'!A:A).

I would like to drag this formula down such that cell B3 has

=Sumif(array, criteria, 'Sheet 1'!B:B).

I would also need this to be robust against dragging across the columns. ie. if I were to then drag across to cell C3, I'd like it to be

=Sumif(array2, criteria2, 'Sheet1'!B:B).

It seems to come down to inputting a number to produce an array, such that when this gets dragged down the rows, the number increases by 1 and thus the column of the sumrange increases by 1. But I just can't get it to work.

Any thoughts?

Answer 1

Here/screeshot(s) refer:

M1: sumif/offset

=SUMIF(A:A,F$4,OFFSET($C:$C,0,ROWS(J$4:J4)-1,ROWS(C:C),1))

Notes: dragging J4 to right updates criteria range and criteria, but leaves sum range (A:A) unchanged. Dragging down updates only sum range (from 1 -> 2).

---

M2: sum/offset

=SUM(IFERROR((A:A=F$4)*(OFFSET($C:$C,0,ROWS(J$9:J9)-1,ROWS(A:A),1)),))

Notes: due to 'lazy range referencing' (e.g. column A, C etc.) - iferror required within sum to mitigate instances where cells do not contain legitimate values for this fn. - see overall note section below

---

M3: sum/index

=SUM(IFERROR(1*(A:A=F$4)*INDEX($C:$D,0,ROWS(J$15:J15)),))

---

General notes/caveats etc.

• Ranges used in your e.g. would conflict given B2 contains initial sumif fn, with sum range immediately to left (A:A), then dragging down would attempt to sum column B (i.e. circular reference) - have updated ranges to something more purposeful accordingly). Appreciate you only provided illustrative e.g. so no sweat ?
• Various other methods exist e.g. sumproduct, or substitute offset in (1) with index (2) for same outcome; General principle: use reference eqn (offset/index/direct reference, etc.) s.t. column offset = rows
• RE: 'robust' - simply use bullets [i.e. $] to fix columns (only for sum range; other ranges assumed to be adjacent to one another given info providdd - these can be modified in similar fashion as sum range (i.e. using index/offset etc.) as req.
• This assumes sum range (given as Sheet1 A:A in your Q) identical to array / criteria range (would advise using specific range to avoid intensive calc delays - table functions, named ranges, '#' Spill references [if you have 365 Office compatable version Excel) per here etc.)
• (shared file link - onedrive secure - provides example of sum/filter/offset (requires Office 365) - not included above as three methods already provided (only 1 requested/req.)
• Checks substantiate veracity/accuracy of these fns as req.

Ta

---