How to use INDIRECT with SUMIFS for criteria range?

Question

I'm working on an excel sheet that has an elastic range. Instead of using A:A as a range, I want to use INDIRECT and 2 cells to determine the range.

Currently I'm stuck with this =SUMIFS(INDIRECT("$B"&B9):INDIRECT("$B"&B10),$A$12:$A$33,D$11)

What would be the correct way to use INDIRECT to determine a range with 2 cells?

Solution

Your formula returns an error because the ranges in a Sumifs need to be the same size, but the first range is of variable length, due to the construction with Indirect(), whereas the second range is a fixed range.

Indirect() is one possibility to build variable ranges, and you can apply the same technique to the second range, like this:

=SUMIFS(INDIRECT("$B"&$B9):INDIRECT("$B"&$B10),INDIRECT("$A"&$B9):INDIRECT("$A"&$B10),D$11)

Note that I added $ signs for the B9 and B10 references, so the column does not change when the formula is copied across.

Be aware that Indirect is volatile, though and excessive use of it in large workbooks can lead to slowness. Another way to write the formula is with the non-volatile Index(), like this:

=SUMIFS(INDEX($B:$B,$B9):INDEX($B:$B,$B10),INDEX($A:$A,$B9):INDEX($A:$A,$B10),D$11)

Comments

[Admin]

What's wrong with the formula you wrote?

[Admin]

@fixer Maybe that it does not work???

[Admin]

@teylyn, I was focused on the INDIRECT and didn't even catch that. Good answer.

[Damian]

The main sheet I'm working on has some 150K rows, you gave me more than I asked for and I thank you! The reason why I was thinking of using a variable range is because of performance.

[teylyn]

Sumifs() is super fast and is happy to process whole columns. No need to jump through hoops to restrict the range you feed it, in fact, that might adversely impact performance. In any case, if that solved your issue, please mark it as the answer as described in the Tour

[Damian]

In the sheet I'm working on, I'm trying to reference the INDEX on another page, the original code is this: =COUNTIFS('Stay Data'!$A$2:$A$200000,'Stay Report'!$A6,'Stay Data'!$F$2:$F$200000,">="&$A$1,'Stay Data'!$F$2:$F$200000,"<="&$B$1,'Stay Data'!$P$2:$P$200000,'Stay Report'!D$5)

[Damian]

I'm trying to do this: =COUNTIFS('Stay Data'!Setup!index($B:$B,$B$31):index($B:$B,$B$32),'Stay Report'!$A6,'Stay Data'!Setup!index($B:$B,$B$31):index($B:$B,$B$32),">="&$A$1,‌​'Stay Data'!Setup!index($B:$B,$B$31):index($B:$B,$B$32),"<="&$B$1,‌​'Stay Data'!Setup!index($B:$B,$B$31):index($B:$B,$B$32),'Stay Report'!D$5) How do I do this portion correctly 'Stay Data'!Setup!index ?

[Damian]

I've tried using sumifs with an entire range using A:A but that affected performance badly. Do you have any suggestions for rows about 200,000?

[teylyn]

Please don't ask follow-up questions in comments. Your question was answered, so please mark the answer. Then start a new question.