SUMIFS based on dynamic Sum_Range

Question

I want to use SumIFs formula where i want to use dynamic sum range.

column range is selected from another sheet in same workbook base on a cell value in current sheet

My Formula:

=SUMIFS ( <XYZ> , CriteriaRange1, Criteria1, CriteriaRange2, Criteria2)

I want <xyz> to be dynamic column range selection based on a cell value.

It seems like you'd have to have dynamic criteria ranges as well, or you'll get a #VALUE error if they're not the same number of rows. — Doug Glancy
What exactly is the cell value on which the dynamic range should be based and how does that give you the range? — barry houdini

Jerry Jerry · Accepted Answer · 2013-05-30T14:14:10

You can use INDIRECT()

If, say, your formula is in Sheet2 cell A1, it bases itself on a cell B1 in which there is Sheet1!A:A, you can use:

=SUMIF(INDIRECT(A2), CriteriaRange1, Criteria1, CriteriaRange2, Criteria2)

If cell B1 has only A, you will have to construct the address from the INDIRECT() function:

=SUMIF(INDIRECT("Sheet1!"&A2&":"&A2), CriteriaRange1, Criteria1, CriteriaRange2, Criteria2)