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!

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" <(E-Mail Removed)> wrote in message
news:BF5630E5-5D3E-406E-862E-(E-Mail Removed)...
>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!
>

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" <(E-Mail Removed)> wrote in message
> news:BF5630E5-5D3E-406E-862E-(E-Mail Removed)...
> >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!
> >
>
>
>

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"acyakos" <(E-Mail Removed)> wrote in message
news:8405BC64-CB1D-47B4-B52C-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:BF5630E5-5D3E-406E-862E-(E-Mail Removed)...
>> >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!
>> >
>>
>>
>>