Double dynamic named range

[Shogun]

Im trying to make a dynamic named range that gives me a dynamic range whithin a dynamic range and its giving me a headache i cant find any examples out there of trying to do it twice. Help me before my head explodes!

 

[AlphaFrog]

If you just want to SUM based two criteria

Excel 2007 or later
=SUMIFS(B:B, A:A, "Name2", C:C, 3)

Excel 2003 or earlier
=SUMPRODUCT(--(A1:A100="Name2"), --(C1:C100=3), (B1:B100))

 

[Shogun]

If you just want to SUM based two criteria

Excel 2007 or later
=SUMIFS(B:B, A:A, "Name2", C:C, 3)

Thanks this does work for this and should help me but I still need to figure out how to do this the named range way for charting reasons.

 

[T. Valko]

Thanks this does work for this and should help me but I still need to figure out how to do this the named range way for charting reasons.

You haven't explained what criteria is to be used to define the range.

 

[Shogun]

You haven't explained what criteria is to be used to define the range.

Its in the picture but basically i need to have a range based on a value in column a and then within that range select another range in column C based on a value from that column. So basically a dynamic range within a dynamic range.

 

[Aladin Akyurek]

Im trying to make a dynamic named range that gives me a dynamic range whithin a dynamic range and its giving me a headache i cant find any examples out there of trying to do it twice. Help me before my head explodes!

Define the following one by one by means of
Formulas | Name Manager (Excel 2003 and older systems: Insert | Name | Define).

The data sample you posted is assumed to be in A:C on Sheet1, starting at row 2
with headers in row 1.

BigNum:

=9.99999999999999E+307

Lrec:

=MATCH(BigNum,Sheet1!$B:$B)

Family:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Lrec)

Actuals:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,Lrec)

Mweek:

=Sheet1!$C$2:INDEX(Sheet1!$C:$C,Lrec)

Data:

=Sheet1!$A$2:INDEX(Sheet1!$C:$C,Lrec)

SubRange:

=OFFSET(INDEX(Family,MATCH(1,IF(Family="Name2",IF(Mweek=3,1)),0)),0,0,SUM(IF(Family="Name2",IF(Mweek=3,1))),COLUMNS(Data))

SubRange defines precise that subarea your criteria delimits... If needed, COLUMNS(Data) can be replaced with 3, the width of the data area.

=SUM(INDEX(SubRange,0,2))

would some Actuals (column 2 of the range), yielding a total of 404.

 

[T. Valko]

Its in the picture but basically i need to have a range based on a value in column a and then within that range select another range in column C based on a value from that column. So basically a dynamic range within a dynamic range.

Ok, do you want the range to be defined to include all 3 columns or are you just interested in the numeric values in the Actuals column?

For example...

Should the range for Mweek 3 and Name2 be A17:C19 or B17:B19?

 

[markmzz]

Shogun,

Try the following:

Assuming that the name of your worksheet is Plan1.

Create the following names:

Family =OFFSET(Plan1!$A$2,0,0,COUNT.VALUES(Plan1!$A:$A)-1)
Actuals =OFFSET(Family,0,1)
Mweek =OFFSET(Family,0,2)

Then, type the following formula (array - CTRL+SHIFT+ENTER) in cell F8 (Total).

=SUM(IF(Mweek=E8,IF(Family=F8,Actuals,0),0))

Markmzz

 

[T. Valko]

Shogun,

Try the following:

Assuming that the name of your worksheet is Plan1.

Create the following names:

Family =OFFSET(Plan1!$A$2,0,0,COUNT.VALUES(Plan1!$A:$A)-1)
Actuals =OFFSET(Family,0,1)
Mweek =OFFSET(Family,0,2)

Then, type the following formula (array - CTRL+SHIFT+ENTER) in cell F8 (Total).

=SUM(IF(Mweek=E8,IF(Family=F8,Actuals,0),0))

Markmzz

What is COUNT.VALUES?

That's not a native Excel function.

Maybe you meant COUNTA?

 

[markmzz]

Shogun,

I'm sorry, the correct formula is:

Family =OFFSET(Plan1!$A$2,0,0,COUNTA(Plan1!$A:$A)-1)

In my version of Excel the formula is:

Family =DESLOC(Plan1!$A$2;0;0;CONT.VALORES(Plan1!$A:$A)-1)

Markmzz
<!-- / message -->