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 ?????

 

[Aladin Akyurek]

Oh, Can't believe it's you, Aladin.
Thanks for stopping by.

You are welcome.

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?

To mention a few such points:

1) They might cause performance degradation if used indiscrimantly. If the ranges in question are (almost) fixed, there is no need for such definitions.

2) Harder to work with from within a workbook when such names are in an external workbook.

3) They indeed can't be used with INDIRECT, due to the syntax of this function.

 

[LearnMeExcel]

thanx aladin
you changed my Thoughts about dynamic ranges
thank you my teacher