Using SUM, ADDRESS, INDIRECT in an array not working

[Bradley]

Hi,

Here's the problem (I simplified the formula a little to make it easier to
understand.)

=SUM(IF(AreaLookup_RD=1,INDIRECT("Area"&AreaLookup&"!B16"),0))

I am trying to add values in different worksheets if they are assigned to an
area #. I want the values in:

Area1!B16 (assigned to Region 1)
Area2!B16 (assigned to Region 1)
Area3!B16 (assigned to Region 2)
Area4!B16 (assigned to Region 2)
Area5!B16 (assigned to Region 3)
Area6!B16 (assigned to Region 3)
Area7!B16 (assigned to Region 3)

So I have a lookup table:

Column 1 = Region #
Column 2 = Area #

The formula above uses the lookup table to figure out which Area #'s to sum
up.

For some reason Excel is only doing the Array on the first worksheet and not
doing the Array in the INDIRECT function. So it basically adds up a bunch of
Area1!B16 instead of using the correct Area # to go to the right worksheet


I also tried rewriting it to:
=SUM(IF(Lookup!$I2:$I$30=1,"=INDIRECT("&ADDRESS(16,2,1,TRUE,"Area"&Lookup!$J$2:$J$30)&")",0))
- and -
=SUM(IF(Lookup!$I2:$I$30=1,ADDRESS(16,2,1,TRUE,"Area"&Lookup!$J$2:$J$30),0))

Any help would be greatly appreciated!

Thanks,
Bradley