Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count(if(... using array formula: can I use a named range in my ca | Excel Worksheet Functions | |||
Use named ranges in array formula | Excel Worksheet Functions | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
Named range into an array | Excel Worksheet Functions | |||
Array to named range conversion... | Excel Discussion (Misc queries) |