Reference in a formula

[reddolls]

Hello.

I have following array formula in a spreadsheet I am using:

=SUM((MasterList!$H$9:$H$500 = I$6)*(MasterList!$K$9:$K$500 = $B$7)*SevenEight)

The end part of the formula "SevenEight" references a named selection of cells. What I would like to do is replace that with "EighNine" or "NineTen" depending on the year the user selects.

I am not sure how to get the last part to reference a different range name depending on what a user selects?

Thanks if you can help and sorry if the question is not particularly clear!

Rich

 

[pgc01]

Hi Rich

Not sure I understood exactly so I lay my assumptions:

Assuming

- the range N9:N500 has the dates corresponding to the values
- The user writes the year in B1
- If the user chooses 2006 you want to sum the values of EighNine, if 2007 you want to sum the values of NineTen

The result in B2 is

=SUM((MasterList!$H$9:$H$500=I$6)*(MasterList!$K$9:$K$500=$B$7)*((YEAR(MasterList!$N$9:$N$500)=B1)*(B1=2006)*EighNine+(YEAR(MasterList!$N$9:$N$500)=B1)*(B1=2007)*NineTen))

... as an array formula.

Hope this helps
PGC