auto change sumproduct range

[acyakos]

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!

 

[T. Valko]

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.

 

[acyakos]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!