Countifs(), Indirect(), and dynamic named ranges

[Atroxell]

Hi all,

I have run into a little conundrum.

I have a large data table (>430K records) in which I have defined multiple named ranges--each range being a single column. The named ranges are all dynamic.

There is also one worksheet called "Reference" that summarizes some of the main geographic levels such as City, County, etc. This worksheet is basically a sheet of lists with a count of records for each member of the list.

The workbook is designed to be point-and-click using drop down lists so the user does not have to think much.

From there, I have four summary worksheets: Statewide, County, City and Branch. Each of these worksheets has multiple formulas, all SUMIFS() and COUNTIFS(), that summarize from my data sheet and reference sheet in various ways.

The reason I am posting is because my formulas work fine with just one named range, but fail with more than one. So, by example, this works:

=COUNTIFS(Data!$Q$2:$Q$463777,INDIRECT("Reference!D"&$C$1+1)-6000,EstIncome,A17)

But this will not work:

=COUNTIFS(Counties,INDIRECT("Reference!D"&$C$1+1)-6000,EstIncome,A17)

Even though "Counties" is a named range that represents the exact same range as "Data!$Q$2:$Q$463777".

Consistently, if I put a named range in for the INDIRECT() filter criteria, the formula fails.

Is there some sort of restriction in Excel against using more than one named range in a formula or something? What am I doing wrong?

I am hoping it's something I am doing wrong because I do not want to have to manually edit 500 formulas every time I run this report.

TIA
Al

 

[barry houdini]

It works OK for me, what error do you get?

are you sure that Counties is the same size as EstIncome, how are they defined?

 

[Atroxell]

Barry,

Thanks for the quick response.Looking at the dynamic range definitions I have:

=OFFSET(Data!$A$2,0,19,COUNTA(Data!$A:$A)-1,1)

for the "estIncome" range and

=OFFSET(Data!$Q$2,0,0,COUNTA(Data!$Q:$Q),1)

for "Counties".

I am sure you will notice the first is using a column offset and the second does not. That's because column A will always have a value, whereas Column T will not. So I chose an offset. In the second one, column Q always has a value because it is decided for each record at the time of compilation in the database. So theoretically they should both return a range of the same length.

HOWEVER, you bring up an interesting fact that I had not considered and will dig into--Could the difference in dynamic range definitions account for the #VALUE! error? I will get back to you on that.

Ok, I'm back.

Changing the "Counties" range definition to a mirror of the estIncome definition, i.e:

=OFFSET(Data!$A$2,0,16,COUNTA(Data!$Q:$Q)-1,1)

has solved the problem!

That pesky "-1" difference was staring at me the whole time and I just could not see it.

Thanks Barry!