Variable Range in Formula

[Gabe]

I'm stuck on this one, hopefullly someone can help. I have the following
formula:

=SUMPRODUCT((B2>=$B$5)*(B2<=$B$6))

Well "B2" in this formula is supposed to a variable range, but it wont
calculate right. So I created a bunch of different range names on sheet2
(i.e., B1:B100, A3:A7, etc...), and in B2 of sheet1 I created a combobox with
the validation tool for the user to basically choose the ranges I created. So
if they choose let's say the B1:B100 range in B2, how will that work in the
above formula?

Thanks,
~Gabe

 

[Chip Pearson]

Use the INDIRECT function. E.g.,

=SUMPRODUCT((INDIRECT(A1)>$B$5)*(INDIRECT(A1)<=$B$6))

The INDIRECT function will take the content of A1 as a reference. So,
if, for example, A1 contains the text 'K1:K10' Excel will calculate
the formula as if it were written

=SUMPRODUCT((K1:K10>$B$5)*(K1:K10<=$B$6))

The INDIRECT function can take any text string, built up in any manner
you desire and change it to an actual reference that can be used in a
formula. INDIRECTs can be nested as needed, allowing you to have a
chain of formulas that determine the final reference.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Gabe]

Wait nevermind I think I got it, how about:

=SUMPRODUCT((INDIRECT(B2)>=$B$5)*(INDIRECT(B2)<=$B$6))