CODEWITHSUNDEEP
excelexcel-formula
RaG
RaG

Reputation: 337

SUMIFS based on dynamic Sum_Range

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.

Upvotes: 2

Views: 5189

Answers (2)

Joe Lam
Joe Lam

Reputation: 11

The indirect part should be INDIRECT("'"&A1&"'"&"!E:E") for the apostrophe. It works

Upvotes: 1

Jerry
Jerry

Reputation: 71598

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)

Upvotes: 2

Related Questions