auto change sumproduct range
 

I have the following formula:

SUMPRODUCT(--('IS Data'!$D$3:$D$11072=$A6)*('IS
Data'!$B$3:$B$11072=$C$3),(Act1))


Where I am summing all of the data in the named range "Act1" on the IS Data
tab which meets the criteria that the data in column D = A6 (or 1) and the
data in column B = C3, or "Oklahoma". How do I make the reference to the
name range "Act1" dynamic; i.e. I want to be able to use a dropdown menu or
something like that to change Act1 to Act2, Act 3, Act4, etc. and have the
formula return the correct sum. I have tried referencing a dropdown menu
with these values and I get a #VALUE error.

Any ideas?

Thanks!

auto change sumproduct range
 

Try this:

Assume your drop down list is in cell A1.

SUMPRODUCT(--('IS Data'!$D$3:$D$11072=$A6)*('IS
Data'!$B$3:$B$11072=$C$3),INDIRECT(A1))

However, if your named ranges are dynamic themselves, this may not work.


--
Biff
Microsoft Excel MVP


"acyakos" wrote in message
...
I have the following formula:

SUMPRODUCT(--('IS Data'!$D$3:$D$11072=$A6)*('IS
Data'!$B$3:$B$11072=$C$3),(Act1))


Where I am summing all of the data in the named range "Act1" on the IS
Data
tab which meets the criteria that the data in column D = A6 (or 1) and the
data in column B = C3, or "Oklahoma". How do I make the reference to the
name range "Act1" dynamic; i.e. I want to be able to use a dropdown menu
or
something like that to change Act1 to Act2, Act 3, Act4, etc. and have the
formula return the correct sum. I have tried referencing a dropdown menu
with these values and I get a #VALUE error.

Any ideas?

Thanks!

auto change sumproduct range
 

Perfect! That worked like a charm. Thank you so much!

"T. Valko" wrote:

Try this:

Assume your drop down list is in cell A1.

SUMPRODUCT(--('IS Data'!$D$3:$D$11072=$A6)*('IS
Data'!$B$3:$B$11072=$C$3),INDIRECT(A1))

However, if your named ranges are dynamic themselves, this may not work.


--
Biff
Microsoft Excel MVP


"acyakos" wrote in message
...
I have the following formula:

SUMPRODUCT(--('IS Data'!$D$3:$D$11072=$A6)*('IS
Data'!$B$3:$B$11072=$C$3),(Act1))


Where I am summing all of the data in the named range "Act1" on the IS
Data
tab which meets the criteria that the data in column D = A6 (or 1) and the
data in column B = C3, or "Oklahoma". How do I make the reference to the
name range "Act1" dynamic; i.e. I want to be able to use a dropdown menu
or
something like that to change Act1 to Act2, Act 3, Act4, etc. and have the
formula return the correct sum. I have tried referencing a dropdown menu
with these values and I get a #VALUE error.

Any ideas?

Thanks!

auto change sumproduct range
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"acyakos" wrote in message
...
Perfect! That worked like a charm. Thank you so much!

"T. Valko" wrote:

Try this:

Assume your drop down list is in cell A1.

SUMPRODUCT(--('IS Data'!$D$3:$D$11072=$A6)*('IS
Data'!$B$3:$B$11072=$C$3),INDIRECT(A1))

However, if your named ranges are dynamic themselves, this may not work.


--
Biff
Microsoft Excel MVP


"acyakos" wrote in message
...
I have the following formula:

SUMPRODUCT(--('IS Data'!$D$3:$D$11072=$A6)*('IS
Data'!$B$3:$B$11072=$C$3),(Act1))


Where I am summing all of the data in the named range "Act1" on the IS
Data
tab which meets the criteria that the data in column D = A6 (or 1) and
the
data in column B = C3, or "Oklahoma". How do I make the reference to
the
name range "Act1" dynamic; i.e. I want to be able to use a dropdown
menu
or
something like that to change Act1 to Act2, Act 3, Act4, etc. and have
the
formula return the correct sum. I have tried referencing a dropdown
menu
with these values and I get a #VALUE error.

Any ideas?

Thanks!