Variable Range in Formula

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

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




On Wed, 12 May 2010 09:45:01 -0700, Gabe
wrote:

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

Wait nevermind I think I got it, how about:

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

"Gabe" wrote:

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