Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using sumproduct in a dynamic range? | Excel Discussion (Misc queries) | |||
SUMPRODUCT With External Range Name? | Excel Worksheet Functions | |||
Sumproduct with #N/A in range | Excel Worksheet Functions | |||
How do I change a range name back to the underlying data range? | Excel Worksheet Functions | |||
sumproduct in a range | Excel Worksheet Functions |