sumifs, sumproduct: Do they work with dynamic named ranges?

[Shrikant]

I have created dynamic named ranges using cell reference and index formula. e.g. $a$1:index($a$1:$A$300,counta($a$1:$A$300))

I used those dynamic named ranges in sumifs and sumproduct (because in sumifs it didnt work, I tried sumproduct also) like

=SUMIFS(ORDERS,PRODUCTS,PRODUCT1,DATES,">="&DATE1,DATES,"<"&DATE2)

ORDERS, PRODUCTS and DATES are dynamic named ranges, product1, date1 and date2 are respective criteria in cells.

it gave me value error.
same with sumproduct structure also.

Although I managed the thing without DNRs, but still if someone enlighten me if this will happen with DNR and SUMIFS/SUMPRODUCT ?????

 

[Richard Schollar]

Your ranges are more than likely different sizes

EDIT: or you have a #VALUE! in at least one of the ranges already

 

[Richard Schollar]

Check using ROWS() to count how many rows in each ie:

=ROWS(ORDERS)

=ROWS(PRODUCTS)

=ROWS(DATES)

all three should return the same value.

 

[Shrikant]

Thanks.

 

[Aladin Akyurek]

$a$1:index($a$1:$a$300,counta($a$1:$a$300))

would be dynamic only within A1:A300. Anything beyond A300 won't be included.
Also, COUNTA is risky to invoke if any in-between cell is empty.

 

[LearnMeExcel]

$a$1:index($a$1:$a$300,counta($a$1:$a$300))

would be dynamic only within A1:A300. Anything beyond A300 won't be included.
Also, COUNTA is risky to invoke if any in-between cell is empty.

Hi Aladin
i am using Counta when i make dynamic name
but u said it RISKY
what it is the best way to to be safety with my data when i am using it, special with dynamic names

 

[Aladin Akyurek]

Hi Aladin
i am using Counta when i make dynamic name
but u said it RISKY
what it is the best way to to be safety with my data when i am using it, special with dynamic names

Select a range which houses numeric data if possible like numbers, dates, or hours.

Let column A on Sheet1 house numeric data, say, from A5 on.

Method 1: This invokes INDEX() in the definitions of dynamic ranges...

Define Lrec as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$A:$A)

Define Arange as referring to:

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

Let's assume that the ranges, say, in column B, E, and P are associated with the range in A for which Arange is just defined.

Define Brange, Erange, and Prange as referring to:

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

=Sheet1!$E$5:INDEX(Sheet1!$E:$E,Lrec)

=Sheet1!$P$5:INDEX(Sheet1!$P:$P,Lrec)

Although not quite sure, but it's probably me probably introduced this method at MrExcel

Method 2: This invokes OFFSET() in the definitions of dynamic ranges...

Define VSize as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$A:$A)-ROW(Sheet1!$A$5)+1

The ranges then are defined respectively as follows:

=OFFSET(Sheet1!$A$5,0,0,VSize)

=OFFSET(Sheet1!$B$5,0,0,VSize)

=OFFSET(Sheet1!$E$5,0,0,VSize)

=OFFSET(Sheet1!$P$5,0,0,VSize)

If there is only text ranges available, replace

9.99999999999999E+307 [ BigNum ]

with

REPT("z",255) [ BigStr ]

in the definition of Lrec or VSize.

Note 1. INDEX preceded by a colon [ i.e., : ] in order to connstruct a reference makes this function also volatile.

Note 2. Not sure whether it's a settled matter as to which type of definition is less costly.

Note 3. If all ranges of interest are of mixed type (numeric and text), Lrec and VSize must be determined a bit differently.

Note 4. Just to make explicit the relation between Lrec and VSize:

VSize = Lrec-ROW(Sheet1!$A$5)+1

The definitions regarding Lrec and VSize use thus a faster function (i.e., MATCH) which is not affected by in-between empy cells within a range of interest.

 

[LearnMeExcel]

Hi Aladin
no word can describe how much i respect you
really thanx for all that
but in note 3 when i have a mixed range, should i define Lrec and Vsize at same time or what ??

 

[Aladin Akyurek]

Hi Aladin
no word can describe how much i respect you
really thanx for all that

Thanks for the kind words and feedback.

but in note 3 when i have a mixed range, should i define Lrec and Vsize at same time or what ??

Assuming like before that column A on sheet1 is of mixed type and the data (not the header) starts at A5...

Define LNrec (last numeric record) as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$A:$A)

Define LTrec (last text record) as referring to:

=MATCH(REPT("z",255),Sheet1!$A:$A)

Define Lrec as referring to:

=MAX(IF(ISNUMBER(LNrec),LNrec,0),IF(ISNUMBER(LTrec),LTrec,0))

The latter as in previous set up would allow us to correctly define
Arange with the INDEX method as:

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

Or using the OFFSET method after defining VSize as referring to:

=Lrec-ROW(Sheet1!$A$5)+1

=OFFSET(Sheet1!$A$5,0,0,VSize)

 

[Shrikant]

Thanks for the kind words and feedback.



Assuming like before that column A on sheet1 is of mixed type and the data (not the header) starts at A5...

Define LNrec (last numeric record) as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$A:$A)

Define LTrec (last text record) as referring to:

=MATCH(REPT("z",255),Sheet1!$A:$A)

Define Lrec as referring to:

=MAX(IF(ISNUMBER(LNrec),LNrec,0),IF(ISNUMBER(LTrec),LTrec,0))

The latter as in previous set up would allow us to correctly define
Arange with the INDEX method as:

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

Or using the OFFSET method after defining VSize as referring to:

=Lrec-ROW(Sheet1!$A$5)+1

=OFFSET(Sheet1!$A$5,0,0,VSize)

Oh, Can't believe it's you, Aladin.
Thanks for stopping by.
Can you please share more limitation of dynamic named ranges.
One I know is Dynamic Named ranges does not work with Indirect function, do it?