Using Named Range within an Array Formula
 

I am trying to refer to a named range within an array formula without a great
deal of success - my original formula is as follows:

{=SUM(IF(Data!$A$2:$A$145=$B$6,IF(Data!$B$2:$B$145 =$B7,Data!$C$2:$C$145,0),0))}

I want to change the reference "Data!$$B$2:$B$145" to the dynamicly named
range "Capital_Classification_1" so that if any extra lines of data are added
to the Data worksheet the array formula automatically picks this new data up
without me having to re-set the range in the array.

Is this possible?

Using Named Range within an Array Formula
 

The below will work only if the number of rows in all 3 named ranges are
same...
Try the below with rnga, rngb, rngc all three having same number of rows.

(array entered)
=SUM(IF(rnga=$B$6,IF(rngb=$B7,rngc,0),0))

If this post helps click Yes
---------------
Jacob Skaria


"Ivor Davies" wrote:

I am trying to refer to a named range within an array formula without a great
deal of success - my original formula is as follows:

{=SUM(IF(Data!$A$2:$A$145=$B$6,IF(Data!$B$2:$B$145 =$B7,Data!$C$2:$C$145,0),0))}

I want to change the reference "Data!$$B$2:$B$145" to the dynamicly named
range "Capital_Classification_1" so that if any extra lines of data are added
to the Data worksheet the array formula automatically picks this new data up
without me having to re-set the range in the array.

Is this possible?

Using Named Range within an Array Formula
 

Supposing you have this dynamic range:
Capital_Classification_1
=OFFSET(Data!$A$1,,,COUNTA(Data!$A:$A))

Then you could try this, normal ENTER:
=SUMPRODUCT((OFFSET(Capital_Classification_1,,,)=$ B$6)*(OFFSET(Capital_Classification_1,,1,)=$B$7),O FFSET(Capital_Classification_1,,2,))

(Vary/use the col param in the OFFSET to point to cols A, B, C)

Above lightly tested ok
Success? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Ivor Davies" wrote:
I am trying to refer to a named range within an array formula without a great
deal of success - my original formula is as follows:

{=SUM(IF(Data!$A$2:$A$145=$B$6,IF(Data!$B$2:$B$145 =$B7,Data!$C$2:$C$145,0),0))}

I want to change the reference "Data!$$B$2:$B$145" to the dynamicly named
range "Capital_Classification_1" so that if any extra lines of data are added
to the Data worksheet the array formula automatically picks this new data up
without me having to re-set the range in the array.

Is this possible?