Named ranges with the same formula gives different values

[Waimea]

I am trying to use dynamic named ranges in my workbook,

I have defined several dynamic named ranges but when I evaluate them the ranges are different by 1 row, which in turn makes SUMIFS and COUNTIFS to say #value .

This is the formula for the named ranges that I am using:

=OFFSET(Data!$H$13;0;0;COUNTA(Data!$H:$H)-1;1)

My data starts at row 13 as the header row, which I in turn deduct in the COUNTA formula.

Is there a better formula for dynamic named ranges?

 

[Waimea]

The first named range evaluates to E14:E2233 and the second named range evaluates to F14:F2234?

 

[Waimea]

Any suggestions on what I am doing wrong?

 

[Waimea]

I am still working on this, all suggestions are welcome!

What is the best way to create dynamic named ranges?

 

[Eric W]

That's a perfectly fine way to define a dynamic named range. What is the formula you're using for the other range?

 

[Waimea]

Hi Eric,

thank you for your answer! I am trying to use SUMIFS and COUNTIFS but since the ranges are different I get a #VALUE ! in all my formulas (if I remove the IFERROR).

=IFERROR(COUNTIFS(DynamicRange1;"Criteria1";DynamicRange2;"Criteria2";DynamicRange3;"Criteria3);"")

When I evaluate the formula it evaluates to different ranges and the #VALUE ! is envoked???

 

[Eric W]

You didn't answer my question. What are the formulas you have for DynamicRange1, DynamicRange2, and DynamicRange3? My assumption is that they are the same as the one you posted originally, just using different columns. If that is true, then they could easily evaluate to different sizes if there are a different number of elements in each column. Check for that. If you have a particular column that is the "master", say column X, then adjust your dynamic range formulas to:

=OFFSET(Data!$[COLOR=#ff0000]H[/COLOR]$13;0;0;COUNTA(Data![COLOR=#ff0000]$X:$X[/COLOR])-1;1)

Or if you don't know which column has the most, and you want to go to the end of the largest column, try:

=OFFSET(Data!$H$13;0;0;MAX(COUNTA(Data!$H:$H);COUNTA(Data!$I:$I);COUNTA(Data!$J:$J))-1;1)

Or you could take advantage of the fact that COUNTIFS is "aware" of the last used row of each column and use this as your formula:

=COUNTIFS(Data!$H:$H;"Criteria1";Data!$I:$I;"Criteria2";Data!$J:$J;"Criteria3")

Using dynamic ranges in COUNTIFS doesn't save you any time, although they are very useful in other functions.



FYI: It's a good idea to wait a while (several hours) before posting updates or follow ups to your question. Adding a message to the thread removes the thread from the "Zero Replies" button, so many people won't ever see your question.

 

[Waimea]

Hi Eric,

thank you very much for your reply! I'm going to try your suggestions!

You are correct about the posting but I am really grinding the whole day without figuring out why it doesn't work!

The formulas for the dynamic named ranges are the same but with different columns.

 

[Waimea]

Hi Eric,

your suggestion with the master column worked great and now all my #VALUE ! are back to numbers.

Thank you for your suggestion, it's very clever!

 

[Eric W]

Glad to help!